Averaging miles per gallon

[TckyTina]

I have a yearly truck inventory that I have set up to average the yearly mpg
of fuel used. However, I have blank cells - as I keep these records for the
year - How can I leave blank cells out of averaging?

 

[Zack Barresse]

Blanks should not be counted in an AVERAGE or SUM function. What will throw
off your average is zero (0) values. If that is what you mean, you would
need to either use an array-entered function or a workaround...

=AVERAGE(IF(A1:A10<>0,A1:A10))
(Confirm with Ctrl + Shift + Enter, as it is an array formula.)

... or ..

=SUMPRODUCT(--(A1:A10<>0),A1:A10)/COUNTIF(A1:A10,"<>0")

Ensure your ranges are set to desired and all are the same size.

HTH

 

[Peo Sjoblom]

Probably better to use

=AVERAGE(IF(A1:A10<>"",A1:A10))

in this case it probably won't matter but zero is a valid number in an
average


--


Regards,


Peo Sjoblom

 

[Zack Barresse]

True, if the zeros are actually valid in the data set. Guess I was
understanding that it wasn't valid. An assumption on my part. Thanks for
pointing out though Peo.