Find Highest Value of a Row and make it a 1 If less than highest leave blank.

[Ailg]

Good day, i am attempting to find the highest value from row V12 to V46 and make a 1 in the points field that coincides with the highest number. In the example V21 is the highest and i would like to populate a 1 in the X21 field. If it is not the highest value i would like it to be blank. Thank you.

 

[Ailg]

sorry to bug you again, If i use the formula you helped me with,(=IF((MAX(M4:M38)=@M4:M38)*(@M4:M38<>0),1,0)) i get a 1 in the points column like i want for the highest value from column L. However, if i have a tie like in row M4 and M13 i would like to use the value in row N "Team X" to be the tie breaker. In this case Team4 would be the victor and the point column would reflect a 1 only for Team4. The current formula gives me a 1 in the points column for Team1 and Team4. Thank you.

 

[Fluff]

Try

=IF((MAX(M4:M38)=M4:M38)*(M4:M38<>0)*(MAXIFS(N4:N38,M4:M38,MAX(M4:M38))=N4:N38),1,0)

 

[Ailg]

That worked, thank you very much. Last question. I am now receiving 0 in all rows. I would like only a 1 or 0 in O4, O7, O10..etc.

 

[Fluff]

In that case O4 use

=IF((MAX($M$4:$M$38)=M4)*(M4<>0)*(MAXIFS($N$4:$N$38,$M$4:$M$38,MAX($M$4:$M$38))=N4),1,0)

Then copy that into the other cells, changing the cell reference where needed.

 

[Ailg]

Thank you very much. That did the trick.

 

[Fluff]

You're welcome & thanks for the feedback.