Find best 2 of first 3 when blank cells inbetween

[FreakyGirl]

Hello

I'm having a problems with my worksheet.

A player can play up to 5 times but no less than 2 times in a month.

Out of those 5 times i need to pull the highest 2 scores of the FIRST 3 played.

if a player missed a game, then its a blank space (not a 0 because 0 can be a score) under that day.

example:
a players scores for the 5 times may be
3 8 14 3 1 in which case the highest 2 of the first 3 played are 8 & 14. simple enough.

but when a day is missed such as
6 miss 10 13 14 i cant figure out how to get it to show 10 & 13 as the highest two of first 3.

in the attached worksheet
column AU-AY shows the scores for the month extracted from the table. this cant be edited.

starting with BA-BZ are my attempts at helper columns for making it work.

In column BG i have successfully been able the show the first number of the 3 even if there's a blank space in the adjacent score.

column BH i tried to pull the second number but if the first was a blank, that doesn't work as it should.

i get results but not from the right column.

example from row 23. scores are blank 10 10 8 blank 7. I need to get 10 10 8
results give me 10 10 7 using the formula

=IF(BI23<>"",BI23,IF(AX23="",AX23,AY23))

If i change the AX23 with AY23 in the formula I get the 8 needed but when I copy formula to the rest of the cells in that column it changes others to not be right.

column BF shows the results of my attempts in extracting those scores.
column BE shows the 3 scores i need for each row manually put in.
column BD shows if BE & BF match so i can see where its wrong.

there has to be an easier way than what im doing. im beyond frustrated.

thanks in advance,
FreakyGirl

 

[r2c2]

@FreakyGirl
Interesting question.

You can use SMALL & LARGE formulas combined with INDEX to answer the question.

Assuming your data is in B:F, you can this formula in adjacent column to get highest number among the first 3 non-blank values.

=LARGE($B4:INDEX($B4:$F4,IFERROR(SMALL(IF(LEN($B4:$F4)>0,COLUMN($B4:$F4)-COLUMN($A4)),3),5)),1)

For second highest number, just change the 1 to 2.

How it works?

We first fetch the position of 3rd non blank value among B:F using
SMALL(IF(LEN($B4:$F4)>0,COLUMN($B4:$F4)-COLUMN($A4)),3)
If this results in error (ie there are less than 3 values in that range,
We just return 5. This is done by IFERROR(SMALL(...), 5) portion.

We then return the corresponding list by feeding this position to INDEX formula, like this:
$B4:INDEX($B4:$F4,IFERROR(SMALL(...), 5))

We then get the first and second values in this list using LARGE.

I have set up my answer in a new worksheet (as your sample sheet is too busy and I am not sure where you want the answers)

 

[Nebu]

Hi:

If you are interested in macro version.

Thanks

 

[FreakyGirl]

Hello r2c2

Thanks for the solution & the explanation.

I never thought about using the LEN() to check if cell was empty.

You also saved me time by using the LARGE() in this formula. Once I got the 3 values, I was going to use LARGE() to put them in order.

I'm not quite sure I understand the 5 on the IFERROR thou. Why a 5?

In addition, I have a range with less than 3 values where the 5 would be but it shows as #NUM instead.

I should mention that I had to adjust your code to work with my version of excel. I have to use IF(ISERROR()) instead of IFERROR.

=LARGE($AU11:INDEX($AU11:$AY11,IF(ISERROR(SMALL(IF(LEN($AU11:$AY11)>0,COLUMN($AU11:$AY11)-COLUMN($AT11)),3)),5,SMALL(IF(LEN($AU11:$AY11)>0,COLUMN($AU11:$AY11)-COLUMN($AT11)),3))),1)

Perhaps that could be why I get #NUM. I don't mind it, just wondering why its not a 5.

I have attached my workbook again with your code implemented in COLUMNS BA, BB, BC.

Thanks for your time & assistance.
FreakyGirl

 

[FreakyGirl]

Hello Nebu.

Thanks for the macro version but I'm not familiar with scripting or any sort of macros or the script editor.

I appreciate the effort.
FreakyGirl

 

[r2c2]

@FreakyGirl

Regarding 5: If a range has fewer than 3 numbers, then by definition there is no 3rd smallest number. In this case, SMALL returns #NUM!, which will confuse INDEX formula. So we are using 5 to say to index that if SMALL has an error, just use the entire range of 5 numbers.

You will still get an error when your range has no values. I have assumed that your range will have at least 2 values (this is what you said in your original question).

 

[FreakyGirl]

Hello.

Ok. I understand now, ty.

I'm sorry, I wasn't more clear in my question. I did say that a player can play up to 5x times a month but no less than 2.

There will be times when only those 2 scores will be available.

I don't mind the #NUM error as it doesn't effect anything elsewhere.

Thanks again for your help & explanations.
FreakyGirl