Best formula for required result

[Marvo]

Hi, I'm looking for the best formula to achieve the required result.

I've made a simple mock up. What I want is a formula in D11 to find the highest score of handicappers 18 and under. Then in D12 the highest score of handicappers 19+. If possible if there are more than one winner in each category, I'd like a return of zero or "".

Many thanks.

 

[Marvo]

I know, I only put the scores in on competition days. That's why I just wanted "" returned when no score has been entered. However if you experiment and put a score in D, which returns a score in E, your formula changes from a CALC error to a NUMB error.

 

[Fluff]

That's why I just wanted "" returned when no score has been entered.

Then why didn't you say that, rather than complaining about errors?

However if you experiment and put a score in D, which returns a score in E, your formula changes from a CALC error to a NUMB error.

Only if you have one score for <=18. Put in two or more scores & it will work.

 

[Marvo]

I didn't complain, just reported back the result. I would never complain to someone helping me.

Anyway I've managed to achieve the result I required by doing it in stages.

1) Created a new column which references the handicap column and results in either HIGH or LOW.
2) A MAXIFS cell to work out the highest score in both categories.
3) A COUNTIFS cell to work out how many high scores there were in each category.
4) Then if the answer is 1, the XLOOKUP function transfers the corresponding player to the prize winners sheet.

Bit of a long winded way of going but got there in the end.
Thanks to everybody who tried to help.