Averages ignoring zeros

[ctomblin]

I need to calculate "average number of loads per day" for any given number of days. ie. over a 7 day period I only want the average calculated against active days. For example if production only takes place on 3 of the 7 days then the average should be calculated against 3 days not 7.

 

[elgringo56]

If you have values in say, A1 down thru A7, but some of the cells in that area are blank, no entries, =AVERAGE(A1:A7) will only look at the cells that have values in them. So, if you take any cells that have zeros in them and make them blank ("") instead of zero, you will only average the cells with numbers.

 

[jimboy]

or use this;

=SUM(B1:B4)/COUNTIF(B1:B4,"<>0")

 

[ctomblin]

Thanks guys, =average is fine. I didn't realise XL ignored empty cells.