The Power Loon
New Member
 Joined
 Feb 7, 2020
 Messages
 30
 Office Version

 365
 Platform

 Windows
I am currently averaging values in one table based off of their rankings in a separate table. I am doing so for each combination of the potential rankings. The formula below works for this purpose.
=AVERAGE(IF((Table2[ranking]=B2)+(Table2[ranking]=C2)+(Table2[ranking]=D2),Table3[Value]))
However, I want to also factor in the category into which the values fell. When I add "Category" to the formula, it doesn't return the correct values.
=AVERAGE(IF((Table2[ranking]=B2)+(Table2[ranking]=C2)+(Table2[ranking]=D2)+(Table5[category]=$L$2),Table3[Value]))
Attached is a picture with a small sample of data for demonstrative purposes. As you'll see in the picture, A2 should be the average of all values with a ranking of "1" AND a category of "1st to 2nd" (result should be 102.25). However, i am getting a different value that I have no idea how i'm getting.
Any ideas about what i'm doing wrong with this formula?
=AVERAGE(IF((Table2[ranking]=B2)+(Table2[ranking]=C2)+(Table2[ranking]=D2),Table3[Value]))
However, I want to also factor in the category into which the values fell. When I add "Category" to the formula, it doesn't return the correct values.
=AVERAGE(IF((Table2[ranking]=B2)+(Table2[ranking]=C2)+(Table2[ranking]=D2)+(Table5[category]=$L$2),Table3[Value]))
Attached is a picture with a small sample of data for demonstrative purposes. As you'll see in the picture, A2 should be the average of all values with a ranking of "1" AND a category of "1st to 2nd" (result should be 102.25). However, i am getting a different value that I have no idea how i'm getting.
Any ideas about what i'm doing wrong with this formula?