MS Excel Formula HELP!

Factual Questions
1

Please help. I have a spread sheet that I am using to monitor a budget over the calendar year. I have all of the calculations to add up to the monthly invoice amounts in cells m26 - bl26. Since it is only February now, only January has been calculated and invoiced. All of the other monthly totals are still at $0.00.

I would like a cell that calculates the total spent “average” over the calendar year. I have this formula so far =SUM(M26:BL26)/12. The problem is, is that it calculates 1/12th of January’s total currently.

How do I correct the formula to calculate the average of only the cells that have dollar figures in them without having to modify the formula 12 times through the year?
KnK

2

You can use something like:

=IF((B13>0),B13*2,“N/A”) for the entry (if the value is greater than zero do the calc, otherwise put in “N/A”) and then use AVERAGE(B1:B12) to get the average, since it will ignore the text strings.

Obviously adjust for your own values. Does this make sense?

3

Re-reading it, it seems it doesn’t make much sense. It’s like, if the formula for your M26 was

=SUM(M2:M24)

or something, then replace it with

=IF((SUM(M2:M24)>0),SUM(M2:M24),“N/A”)

and similarly for each month. Now when you do AVERAGE(M26:BL26) it ignores all the ones with “N/A” in and just does the ones with values.

I’m sure there must be a simpler way to do this, but this one does work.

4

If you change 12 in the formula =SUM(M26:BL26)/12 to (MONTH(NOW())-1) it will divide by the month number of last month, in February 1, in March 2 etc. Do you see how that works?

5

Here’s another way (though I’m sure there’s a better one still - probably a singe command, knowing Excel).

Reserve another column/s, let’s say the Z column. Each cell in this column should respectively contain

=if(M2>0,1,"")

(each cell respectively represents one month in the first column/s).

At the bottom, say Z25, you have =sum(Z2:Z24) - i.e. the number of non-zero months.

Then your averaging formula is simply

SUM(M26:BL26)/Z25

Of course this messes up if there is a genuine zero month…