Sum based on array

Question

I'm trying to make a validation if in the competition of badminton a setup is valid. A setup is valid if the sum of the 2 player rankings is higher or equal then the sum of the players below

ranking is as following:

A, B1, B2, C1, C2, D with A being the highest en D being the lowest

This is probably easiest by giving D an index code of 1, and C2 = 2, ..., A = 6

when you have this setup:

+---------+---------+
| Name    | ranking |
+---------+---------+
| player1 |    A    |
| player2 |    B2   |
+ --------+---------+
| player3 |    B1   |
| palyer4 |    B1   |
+---------+---------+

So in excel how can I validate that the sum of player 1 & 2 is higher or equal then the sum of player 3 & 4

user4039065 · Accepted Answer · 2014-11-01 12:37:31Z

2

You can assign a value to the ranking with a hard-coded LOOKUP. e.g. =LOOKUP(B2,{"A","B1","B2","C1","C2","D"},{5,4,3,2,1,0}). Result is 5. Note that the rankings are in ascending order.

This could be used within a SUMPRODUCT on both members of the team for the total team ranking. e.g. =SUMPRODUCT(LOOKUP(B2:B3,{"A","B1","B2","C1","C2","D"},{5,4,3,2,1,0})). . Result is 8.

Compare the total from one team against the total of the other for a TRUE/FALSE. e.g. =SUMPRODUCT(LOOKUP($B$2:$B$3,{"A","B1","B2","C1","C2","D"},{5,4,3,2,1,0}))>=SUMPRODUCT(LOOKUP(B4:B5,{"A","B1","B2","C1","C2","D"},{5,4,3,2,1,0})). Result is TRUE.

An IF statement could provide some text results that might be more favourable than a simple TRUE/FALSE return.

answered Nov 1, 2014 at 12:37

user4039065

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum based on array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related