On 07/31/10 19:50, Richard Detwiler wrote:
> James wrote:
>> On 07/31/10 17:26, Johnny Rosenberg wrote:
>>
>>> 2010/7/31 James <bjloc...@lockie.ca>:
>>>
>>>> On 07/31/10 13:09, James wrote:
>>>>
>>>>> I want the average of all the non-empty cells in a column 'c'
>>>>> starting
>>>>> at row 3.
>>>>> I tried:
>>>>> =AVERAGE(C3:c$(count(C3:C51)+3))
>>>>>
>>>>> I'm sure it is simple.
>>>>> I need to make c$(count(C3:C51)+3) a cell reference.
>>>>> 'c15' as a non-formula, count(C3:C51)+3 = 15.
>>>>>
>>>> Found the solution:
>>>> *=AVERAGE(C3:INDIRECT("c"&(COUNT(C3:C51)+3)))*
>>>>
>>> I have to say that I really don't understand what you are doing here
>>> and why. I thought that you wanted to calculate the average value of
>>> all the (non empty) cells in column C starting at C3. That's obviously
>>> =AVERAGE(C3:C65536).
>>>
>>> Your formula, =AVERAGE(C3:INDIRECT("c"&(COUNT(C3:C51)+3))), if
>>> COUNT(C3:C51)+3) = 15, will calculate =AVERAGE(C3:C15), which is not
>>> the whole C column. Also, the size of the range will change if you
>>> change something in C3:C51. What are you actually trying to accomplish
>>> here?
>>>
>>>
>> 51 is an arbitrary row.
>> The =average function seems to treat empty cells as 0.
>>
>>
>>
>>
>
> ??? I just did a check on this, and the "average" function does NOT
> treat empty cells as 0. I entered the following in column A, starting
> in row 1: 2, 2, blank, blank, 2, 2. Then I entered the function
> =average(A1:A6), and the answer was "2", as expected. It would not
> have been 2 if the function treated blank cells as zeros.
>
>
Well it did but I recompiled it and now it doesn't.
That's simpler. :-)
Thanks.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
