Average of a set of numbers, excluding empty cells (but including cells with a value of zero)

Question

Level 1

4 points

Average of a set of numbers, excluding empty cells (but including cells with a value of zero)

Hi, I have two tables in my spreadsheet (a personal budget) showing all my expenses spread over time. In the first table, each column is a month and year, each row is a type of expense (gas, rent, groceries, etc).

October 2013	November 2013	December 2013	February 2014
Income	3000.00	3200.00	2800.00
Gas	85.00	95.00	75.00
Rent	1200.00	1200.00	1200.00
Groceries	110.00	130.00	150.00
Eating Out	92.00	76.00	80.00
Clothes	0.00	90.00	30.00

The second table is this month's expenses so far (I enter the figures for "X Month, So Far" in a third table, which doesn't factor in here).

December 2013, So Far	Average +/-
Income	2800.00	-200.00
Gas	75.00	-15.00
Rent	1200.00	0
Groceries	150.00	+30.00
Eating Out	80.00	-4.00
Clothes	30.00	-15.00

I want the correct formula for the Average +/- column. I want it to calculate the monthly average for that particular type of expense, and then calculate the difference from this month's expenses so far. The key is that I want it to add all the sums from a particular row with any value in their cells, even if it's zero. For instance, all of the examples above would divide the sums by two - even the row for Clothes, which has a cell with 0.00, but I would need that to count as a value.

Also, when calculating the average, I would want the formula to exclude the current month, which is entered automatically in the next column.

I would need a formula I wouldn't have to change every month. Can you help?

iWork Numbers-OTHER

Posted on Dec 23, 2013 10:30 PM

Reply

Answer 1

Barry

Level 9

60,023 points

Dec 24, 2013 12:45 AM in response to shineykid

Hi sk,

"The key is that I want it to add all the sums from a particular row with any value in their cells, even if it's zero. For instance, all of the examples above would divide the sums by two - even the row for Clothes, which has a cell with 0.00, but I would need that to count as a value."

What you've described is the default condition for AVERAGE over a range of cells—any cell containing a numerical value is included, and cell that is empty or contains a text value is ignored. Here are some examples:

Creating a constant formula that will ignore the current (and future) month(s) can be done by ensuring that the amounts in the current month are seen as Text, rather than as numbers, as I've done in column C of row 6.

This can be done using a formula to transfer amounts from the 'third table' you mention to the first table in your example (showing monthly expenses for earlier months and partial totals for the current month). The formula would use IF to add text to the begining or end of the totals IF the column was for the current month, but to transfer of=nly the monthly totals for previous months.

=IF(earlier-month,"xx"&formula,formula)

Both instances of formula are the same—whatever formula you are currently using to transfer the amounts to this tabe.

An alternate method would be to add a second header row to the top of the forst table, and place checkboxes into thoes cells. Check the box when you want to incude that column's values in the averages.

For this method, AVERAGE would be replaced with AVERAGEIF, with the 'IF' condition a simple reference to the row of checkbox cells. As each month passed, the only change needed would be to check the box in the just-passed month's column.

Regards,

Barry

Reply