View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Zack Barresse Zack Barresse is offline
external usenet poster
 
Posts: 124
Default 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?