"Average(If" not working

[The Power Loon]

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?

 

[jasonb75]

I think this is what you want.

=AVERAGE(IF(((Table2[ranking]=B2)+(Table2[ranking]=C2)+(Table2[ranking]=D2))*(Table5[category]=$L$2),Table3[Value]))

 

[The Power Loon]

I think this is what you want.

=AVERAGE(IF(((Table2[ranking]=B2)+(Table2[ranking]=C2)+(Table2[ranking]=D2))*(Table5[category]=$L$2),Table3[Value]))

Thank you for the response.

Unfortunately, that did not work, though it did return different values than before.

 

[jasonb75]

Unfortunately, that did not work,

I suspect that it works just fine. I used the same references as the formula in your post, which is looking at 2nd to 3rd, not 1st to 2nd as you described.

 

[The Power Loon]

I suspect that it works just fine. I used the same references as the formula in your post, which is looking at 2nd to 3rd, not 1st to 2nd as you described.

You are correct. I didn't realize i had it set for $L$2 instead of $L$1. Thank you for your help