# MS Excel Formula HELP!
**URL:** https://boards.straightdope.com/t/ms-excel-formula-help/227648
**Category:** Factual Questions
**Created:** [February 5, 2004, 11:26am UTC](https://boards.straightdope.com/t/ms-excel-formula-help/227648 "2004-02-05T11:26:48Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@KnK](https://boards.straightdope.com/u/KnK)
#### Post date: [February 5, 2004, 11:26am UTC](https://boards.straightdope.com/t/ms-excel-formula-help/227648/1 "2004-02-05T11:26:48Z")
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
---
### Author:  [@dylan\_73](https://boards.straightdope.com/u/dylan_73)
#### Post date: [February 5, 2004, 12:05pm UTC](https://boards.straightdope.com/t/ms-excel-formula-help/227648/2 "2004-02-05T12:05:47Z")
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?
---
### Author:  [@dylan\_73](https://boards.straightdope.com/u/dylan_73)
#### Post date: [February 5, 2004, 12:16pm UTC](https://boards.straightdope.com/t/ms-excel-formula-help/227648/3 "2004-02-05T12:16:12Z")
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.
---
### Author:  [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)
#### Post date: [February 5, 2004, 12:23pm UTC](https://boards.straightdope.com/t/ms-excel-formula-help/227648/4 "2004-02-05T12:23:30Z")
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?
---
### Author:  [@jjimm](https://boards.straightdope.com/u/jjimm)
#### Post date: [February 5, 2004, 12:27pm UTC](https://boards.straightdope.com/t/ms-excel-formula-help/227648/5 "2004-02-05T12:27:22Z")
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…