0

I am having trouble with a small excel formula problem. I am trying to use AVERGEIFS. The setup of this function is

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

The catch is that I want the inclusion of a criteria and its range [criteria_range1,criteria1] to be based on whether a data validation list is Yes or No. So for example if the list is currently set to Yes return:

AVERAGEIFS(average_range,criteria_range1,criteria1)

If no then return

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)

I would use an IF function to handle this if I had only one data validation list with Yes and No, but I have several, three to be exact. So I want to set up a formula that will include a [criteria_range,criteria] based on whether its corresponding data validation list is Yes or No.

I hope that makes sense. I am using OSX. Thanks!

EDIT:

enter image description here

In my problem a name is chosen and with it the corresponding attributes from the dataset below. In the average box below I would love to see the average value for rows with the same attributes in John's case its any rows with blue, type1 = 1, AND type2 = 100. This is easy if I just use a regular AVERAGEIFS function.

What I am trying to figure out is how I could ignore one of the attributes and find the average for that group. So if I select ignore yes for type1 then average would return the average value for rows with color = red AND type2 = 100

  • Can you give some examples of what results you want for some sample inputs? – G-Man Feb 20 '15 at 6:12
1

If I understand, something like this should work -

=AVERAGEIFS(E9:E15,A9:A15,B1,B9:B15,B2,C9:C15,IF(ISNUMBER(IF(D3="yes",1,"a")),B3,"<>0"),D9:D15,IF(ISNUMBER(IF(D4="yes",1,"a")),B4,"<>0"))

So basically it's setup to check for "yes" or "no" and return the value to look for or to just look that it's not 0 (essentially including all possibilities).

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.