Averages with formulas in the empty cell

[tannedman]

Hi Everyone, Can use some help here

using Excel 2000

=AVERAGE(L7:L18) doesnt work if an empty cell has formulas in

Calculating an average from 12 cells that contain a total from each month, each cell is Jan , Feb ect, and has a formula in eg =SUM(L7)/4 Feb’s is =SUM(L8)/4 March’s is =SUM(L9)/5 and so on through the rest of the year the /4 or /5 is the number of weeks in that month to give a average for that month.

This gives me a results in column L7 through to L18

In options I have unchecked the Zero value's. but because there is a formula in each of the empty cells the AVERAGE formula calculates all 12 cells not just the populated ones.I need a formula that ignors any hidden formulas.

e.g. Jan’s average total is 15, Feb’s is 14.4, March is 18 but the calculation returns a figure of 3.95 not 15.8. for a running average. can anyone help?

[NBVC]

Maybe

=SUMIF(L7:L18,"<>0")/COUNTIF(L7:L18,"<>0")

or

=AVERAGE(IF(L7:L18<>0,L7:L18)) confirmed with CTRL+SHIFT+ENTER not just ENTER

[tannedman]

Bingo, the second formula did the Trick,
Which part did the existing formula ommission?

....many thanks NBVC.
Great Forum.

[NBVC]

Which part did the existing formula ommission?

sorry? What's the question again?

[tannedman]

I can see the entry to ignor the zero but which part of your formula ignors anything else in the cell like the existing hidden formula?

Cheers anyway, i just like to know why and how things do what they do, or why not.

[NBVC]

All the formula does is omit 0's (and results of formulas that equal 0).... It doesn't ignore the actual formulas....