Spreadsheet average help

January 14, 2008 1:39 PM

I'd like a spreadsheet (OpenOffice Calc) to give me the average of the numbers in a certain row that will be filled over a period of months, and I'd like the formula for the average to remain valid even as I fill it, cell by cell. How?

For instance, in month 2, cells A1 and B1 will have a number; cells C1-G1 will be empty. Cell H1 should display the average of A1 and B1. In month 3, C1 will have a number and D1-G1 will be empty; H1 should show the average of A1-C1, but without me changing the formula in H1 to divide by 3 instead of 2.

I want to be able to do this since I'm using many many different rows of data. I'm guessing this might be possible. Is there a formula to let the spreadsheet know to divide only by the number of non-empty cells in the row?

Thanks.

posted by regicide is good for you to Computers & Internet (7 answers total)

Response by poster: Oh - and not all the rows' data will start in the first month, nor will they necessarily fill up sequentially. Which maybe complicates things.
posted by regicide is good for you at 1:41 PM on January 14, 2008

In excel, the COUNT function gives the number of non-empty cells in a given range. Not sure if there's something similar in OpenOffice, but it works great in excel.
posted by SBMike at 1:44 PM on January 14, 2008

Will a filled-in entry ever be equal to zero?
posted by inigo2 at 1:44 PM on January 14, 2008

At least in Excel, =AVERAGE(range) does exactly what you want, averaging only those cells that are nonempty.
posted by Wolfdog at 1:47 PM on January 14, 2008

Best answer: just use AVERAGE(cell1:cell2)
it ignores empty cells.
posted by plinth at 1:48 PM on January 14, 2008

Best answer: If the "AVERAGE" function works differently in OpenOffice (see Wolfdog's response), then an alternative is to do: SUM(range of cells) / COUNTA(range of cells). "COUNTA" gives you the count of non-empty cells.
posted by inigo2 at 1:50 PM on January 14, 2008

Response by poster: Thank you. The help given by Calc's "function wizard" was less than obvious, though now I understand it.

Much appreciated. You have saved me a great amount of stupid-person work.
posted by regicide is good for you at 1:57 PM on January 14, 2008