Average of an Index(match) combo

[mistrornge]

excel image.png

So I am attempting to use my current formula to cross reference a name and two different values from the same table (statleader) and then average them.


=AVERAGE(INDEX(statleader,MATCH(N50,$B$48:$B$65,0),MATCH(K50:L50,$B$48:$H$48,0)))
As you can see I am getting a #VALUE! error

[TMS]

Please update your version of Excel in your profile. I'm guessing your not still uing 2003.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Typically, INDEX/MATCH will return one ( 1 ) value, so not sure how you hope to get an average.

[mistrornge]

I am using Excel in 365. Updated my account with the new info.

[TMS]

I am using Excel in 365.

That should make life easier. Please upload a sample file, not a picture.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[mistrornge]

Index(Match) being a single number is what I was afraid of.

Updated this post with the actual .xlsx file.
Thanks for your help.

[mistrornge]

Added Paypal payment to encourage assistance.

[TMS]

Is this what you want in M50:

Please Login or Register  to view this content.

copied down.

You can also use this for ALL the rows:

Please Login or Register  to view this content.

NOT copied down.

[TMS]

Incidentally, you CAN do it with INDEX/MATCH, just not the way you were trying to do it.
M50:

Please Login or Register  to view this content.

copied down

Or, for all rows:
M50:

Please Login or Register  to view this content.

NOT copied down

Obviously, 365 gives you FILTER, BYROW and LAMBDA

[mistrornge]

I haven’t had any experience with those commands yet. I will try your suggestions tomorrow when I have the sheet in front of me.

[mistrornge]

Ok my results from trying out the formulas:
=AVERAGE(FILTER($C$49:$H$65,$B$49:$B$65=$N50,"")) says function not valid? The filter function is highlighted as the error.
=BYROW($N50:$N56, LAMBDA(br, AVERAGE(FILTER($C$49:$H$65,$B$49:$B$65=br,"")))) says function not valid? The filter function is highlighted as the error.
=AVERAGE(INDEX($C$49:$H$65,MATCH($N50,$B$49:$B$65,0),0)) gives an answer of 1.5 for every row. Linzi should be 2.5, Amiri should also be giving 2.5, Valerie 0.5, Harrim 0, Jubilost 2.5, Octavia 3.5, & Ekundayo 3
=BYROW(N50:N56, LAMBDA(br, AVERAGE(INDEX($C$49:$H$65,MATCH(br,$B$49:$B$65,0),0)))) this one get the #NAME? error

To test everything I put the formulas into M50 and with the + pull down added the formula to the over cells in the M column. I did this just to test with the formulas you had said to not copy down in case there was one row that got an answer. I also tried the byrow with the spaces and without the extra spaces. Just in case.
I hope I am following directions properly.
Thank you for your help!

[mistrornge]

This is a difficult problem as it has to correlate which stat from columns K & L, then from the N column properly select the corresponding leader and then from B column with the proper stats from row 48. Its a lot to expect. I may be asking more than is possible with excel.

[JeteMc]

Try the following in M50 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Gregb11]

If you are on 365, you should have the FILTER formula.

Try this in cell M50 and copy down.

=AVERAGE(FILTER(FILTER($C$49:$H$65,($C$48:$H$48=K50)+($C$48:$H$48=L50)),($B$49:$B$65=N50)))

[wk9128]

Cell O50 formula , Drag down

Please Login or Register  to view this content.

[mistrornge]

Sorry I missed these answers. I will try them out. Thanks guys.

All work as listed. Is there a way to have them round down? If I open the spreadsheet in another excel version will some of these fail to work?

[JeteMc]

I believe that the Formula that contains the FILTER function will only work for the 365 version, although the 2021 version may also support FILTER.
The other formulas contain functions that are supported at least as far back as the 2003 version.
As to rounding down, the formula from post #12 could be modified to read:

Please Login or Register  to view this content.

Let us know if you have any questions.

[mistrornge]

You guys rock. Thank you

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.