CODEWITHSUNDEEP
excel
SkysLastChance
SkysLastChance

Reputation: 221

Check Multiple Columns for the highest value

So say I had a table like this that has the score of 3 different teams for the week. 

Day   Team1 Team2  Team3
Mon     5     2      2
Tue     0     7      7
Wed     6     3      2
Thu     0     0      1 
Fri    13     6      5

I want a formula that can find the highest score for the day and mark it on a identical table with a value of 1 and mark the other teams 0.

If there are 2 values that are the highest I want them to both be marked 1.

There will never be day with all 0's

Using the data from the table above my other table would look like this. 

Day   Team1 Team2  Team3
Mon     1     0      0
Tue     0     1      1
Wed     1     0      0
Thu     0     0      1 
Fri     1     0      0

I have a working formula

=IF(AND(B2>=$C2,B2>=$D2,B2>=$E2),1,0)

I was just hoping there was a better way to write this formula, so that I can drag it across the teams and have it still work.

If I try to drag my formula now. I have to update the formula for each column. Sometimes I might have 20 + teams.

Any advice is appreciated.

Upvotes: 0

Views: 42

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use MAX():

=IF(B2=MAX($B2:$D2),1,0)

Then copy/drag over and down.

enter image description here

Upvotes: 2

Related Questions