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