• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average with OR

M

msharma864512

Member
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.
 

Attachments

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
 

Attachments

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
 
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
 
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
 
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
 
You must log in or register to reply here.
Back
Top