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]

Not as far as I am aware.

What is in the C column are rounded figures so in C15 the formula is =ROUND(Handicaps!J15,0)

In the E column there is a formula to work out the points scored once the amount of shots are entered in an adjacent column. So in E15 its =IF(D15="","",MAX(2+E$2-D15+(E$3<=C15)*1+(E$3<=(C15-18))*1+(E$3<=(C15-36))*1,0))

It either of these causing the problem?

Column C format is number

Column E format is general.

 

[Fluff]

Those should not cause a problem, but they will return incorrect results, so it would need to be

=LET(f,SORT(FILTER(' Master'!B4:E61,(' Master'!C4:C61<=18)*(' Master'!E4:E61<>"")),4,-1),p,INDEX(f,,4),IF(LARGE(p,1)=LARGE(p,2),"",INDEX(f,1,1)))

 

[Marvo]

That brought up an error I've never seen before, #CALC!

Something about an empty array. I quickly put some figures in but it didn't remove the error.

The problem with handicaps is that although they are exact they are always rounded up or down.

 

[Fluff]

In that case can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Marvo]

I downloaded that and it disappeared, I couldn't find it. I can try again.

I evaluated your formula and it said the problem was in C column. (Empty array) I removed the rounding but that didn't solve it.

 

[Fluff]

If you format col C as dates, what do you see?

 

[Marvo]

All dates. 22/1/1900

 

[Fluff]

Ok, so they are real numbers. You are doing this in Excel rather than Google sheets or some other software?

 

[Marvo]

Excel.

I used to do this quite easily, we have a prize for whoever does the best on each hole (points score), but you have to beat everybody else so its not won that often. The way I did it was I looked for the maximum points number, then saw if it only occurred once. If it did, that person won. At least years AGM they voted that their should be two competitions, 1 for high and 1 for low handicappers. So now I'm stuck as I'm now looking for 2 highest scores, one from each criteria.

 

[Fluff]

In that case I need to see your data to figure out why it isn't working.