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, 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 the $0.00’s into the equation, and reduces my current average to only 1/12th.
How do I correct the formula to exclude the $0.00 cells until they show a real dollar figure?
You should be able to insert a function called “count”, which will find the number of cells that have numerical values in them. Then, replace the 12 in your formula with the cell that has the count function in it.
You may also be able to nest thge count function in your formula, but I would actually have to play with your worksheet to do that.
Yes, the count function should work…unless you are trying to find a daily expense - or something other than the average amount of each expendure…
=counta(M26:BL26) then use that cell in place of 12, as already mentioned…although in rereading your message I’m wondering if you are looking for a daily or monthly average…
Count will only work if the cells for Feb-Dec are null, i.e. empty rather than containing a zero.
Here is something that should work:
=sum(M26:BL26)/countif(M26:BL26,">0")
the COUNTIF function takes any cell in the range (M26:BL26) which meets the conditions(">0") and adds it to the count. I’m not sure why, but you need to put your condition in quotations.
BCE
