Averaging miles per gallon
 

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?

Averaging miles per gallon
 

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

--
Zack Barresse




"TckyTina" wrote in message
...
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?

Averaging miles per gallon
 

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" wrote in message
...
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

--
Zack Barresse




"TckyTina" wrote in message
...
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?

Averaging miles per gallon
 

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. :)

--
Zack Barresse



"Peo Sjoblom" wrote in message
...
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" wrote in message
...
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

--
Zack Barresse




"TckyTina" wrote in message
...
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?