Average Formula

[davinosky]

How does Excel handles "ups & downs values" in the Average( ) formula?


For example: If we have the following series of values: {3,4,6,201,8,65,7,5}, 201 and 65 are ups values.

 

[Hui]

Davinosky


Excel doesn't care about the value of your numbers, that is your role


You can use the Averageifs() function to get around this


eg: If your data is in A1:A8 the following will, help out

=AVERAGEIFS(A1:A8,A1:A8,"<50")


If you want to use a formula to derive a cut off point in B1

then use: =AVERAGEIFS(A1:A8,A1:A8,"<"&B1)


Averageifs is only available in excel 2007-2013


For Excel 97-2003 use:

=SUMIF(A1:A8,"<50")/COUNTIF(A1:A8,"<50")

or

=AVERAGE(IF(A1:A8<50,A1:A8,""))Ctrl+Shift+Enter

 

[davinosky]

Perfect! Thanks for your help!