Conditional Average

[ks1987]

Hi All,

I am trying to calculate average for values in a range of data that fulfill a certain condition viz. >1000 & <=5000.

I am aware that the Averageif function can do the job, however I was unable to figure how to apply it in this particular case.

Any help on this is appreciated.

Thanks,
ks1987

 

[excelliot]

Hi

Refer: AVERAGEIF function - Excel

cheers

 

[ks1987]

Hi,

Thanks for this. I am looking for a particular application of this function, where I need to find the averag between the ranges.

As you would notice in the page it does solve a similar problem viz. =AVERAGEIF(B2:B5,"<23000")

However it is silent in case where, say, one wants to calculate average between 23000 and 30000

 

[skywriter]

Use AVERAGEIFS.

 

[ks1987]

Works. Thanks.

Syntax:
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

=AVERAGEIFS(Table14[Amount],Table14[Amount],">1000",Table14[Amount],"<=5000")
in case of tables

=AVERAGEIFS(B2:B5, B2:B5, ">70", B2:B5, "<90")
In case of "normal" range

 

[excelliot]

Cheers!!