Average with OR

[msharma864512]

Hi,

I am trying to write a formula which can provide me average data from a given range,

In cell H35, i want to calculate average of all the data in the cell H3 to H32, wherever the value in cell G3 to G32 is Substantial.
In cell H36 i want to calculate average of all the data in the cell H3 to H32, wherever the value in cell G3 to G32 is either Moderate or Low.

 

[rahulshewale1]

hii,

see if is corrcet
cell h35 formula copy down.pfa attached sheet.

=SUMIFS($H$3:$H$32,$G$3:$G$32,$G35)/COUNTIF($G$2:$G$31,$G35)


Thank
Rahul shewale

 

[rahulshewale1]

hii,

pfa attached revised formula.

CELL H35 :=SUMIFS($H$3:$H$32,$G$3:$G$32,$G35)/COUNTIF($G$2:$G$31,$G35)

CELL H36:
=(SUMIFS($H$3:$H$32,$G$3:$G$32,$G36)+SUMIFS($H$3:$H$32,$G$3:$G$32,$G37))/(COUNTIF($G$2:$G$31,$G36)+COUNTIF($G$2:$G$31,$G37))


Regard
Rahul shewale

 

[msharma864512]

This is not exactly what I was looking for, the average of Moderate/Low, I need one average wherever the data is for either Moderate or Low

 

[NARAYANK991]

Hi ,

Try the following :

H35 - =AVERAGEIF($G$3:$G$32, G35, $H$3:$H$32)

H36 - =SUM(AVERAGEIF($G$3:$G$32, G36:G37, $H$3:$H$32))

The formula in H36 is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[msharma864512]

Thank you!!! thats what I was looking for

 

[NARAYANK991]

Hi ,

Please check the result for the second formula.

An alternative formula :

=SUM(AVERAGE(IF($G$3:$G$32=TRANSPOSE(G36:G37), $H$3:$H$32)))

entered as an array formula , using CTRL SHIFT ENTER , gives a different result.

Narayan

 

[Naresh Yadav]

Hi,

you can also try this one in H36

=AVERAGE(IF(G3:G32={"moderate","Low"},H3:H32))
pls finish the formula with CTRL+SHIFT+ENTER

 

[NARAYANK991]

Hi ,

The earlier formula should be :

=AVERAGE(AVERAGEIF($G$3:$G$32, G36:G37, $H$3:$H$32))

or

=SUM(AVERAGEIF($G$3:$G$32, G36:G37, $H$3:$H$32))/COUNTA(G36:G37)

entered as an array formula , using CTRL SHIFT ENTER.

Narayan