CODEWITHSUNDEEP
excel
Kiwi
Kiwi

Reputation: 2773

Sum based on array

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

Upvotes: 0

Views: 63

Answers (1)

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Related Questions