James wrote:
On 07/31/10 17:26, Johnny Rosenberg wrote:
2010/7/31 James <[email protected]>:
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.
