averaging varying column entries

Hi
been away from this type of work for a while so am finding myself very rusty


have weekly data pulled from transport figures to give mpg figures, which are then averaged to give a figure for the 4week or a 5week month.

Sheet then displays highest and lowest results in range by driver name


I have just noticed an anomoly when the vehicle is NOT used and returns a no value in the cell range. In this instance the average formula should use the number of weeks there is a figure for as the base for the average

entry line would look like

wk1 wk2 wk3 wk4 AVE
5.1 5.8 6.0 5.6 AVERAGE(WK1:WK4) 5.625
5.1 x 6.0 5.6 AVERAGE(WK1:WK4 - divided by 3 in this case) 5.56 not 4.175

any help appreciated

Can you elaborate as to what "no value" actually constitutes - presumably 0 ?

(any other value - ie blank / non-numeric and the AVERAGE function would work correctly regardless)

Assuming then a 0 value for "no value" then:

Pre XL2007 functions

=SUM(A2:D2)/COUNTIF(A2:D2,">0")

XL2007

=AVERAGEIF(A2:D2,">0")

thanks donkeyote

nearly there - only problem is if all 4 values 0

(actually displays an X something I did on the original weekly sheet - =IF(ISERROR(SUM(O14/(M14/4.546))),"X",SUM(O14/(M14/4.456))),

EDIT:

have changed above to give "0" values - =IF(ISERROR(SUM(O14/(M14/4.546))),"X",SUM(O14/(M14/4.456))),

calculation returns #DIV/0! value which messes up formulae for calculating largest and smallest - can I get it to display "0"

much appreciated

If 0 is preferred then perhaps simplest to encase the divisor within a MAX call

=SUM(A2:D2)/MAX(1,COUNTIF(A2:D2,">0"))

ie divisor never < 1