2nd highest score analysis using vlookup or macros

relianceaaa

Board Regular
Joined
Jan 24, 2014
Messages
76
Hello,
I have stumbled upon quite difficult task, maybe you could give me a hand at solving this task though.
SCORES OF STUDENTS
A1SCORESCORESCORESCORESCORESCORESCORERESULT
ALAN50607090506560FALSE
BORIS56607095506072TRUE
CLIVE60629080406070TRUE
DENVER501009070608077FALSE
EZAN60959080505175TRUE

<tbody>
</tbody>

If H2 >2nd highest number result should be TRUE

1.If 2nd highest number is before 1st highest number ignore it and consider numbers which falls after 1st highest
(here Alan scored 70 marks which is 2nd highest. but it falls before 1st high = 90. consider 65 as 2nd highest and ignore 70 which was before 1st high score)
2.If 2nd highest falls immediately after 1st highest score, ignore that 2nd highest.
(Here Denver scored 90 which is 2nd highest but it falls immediately after 1st score. so consider 80 as 2nd highest)
3.If its a decrement value, dont consider any of the above values till we get an incremental value
(Here EZAN scred 95-90-80 but its in decremental series. so, 1st highest is 95 and 2nd highest is 51 and not 90 or 80)
4.Before 2nd highest number there should be a number which is lesser (Clive 2nd highest 70 . before that 60 which is lesser)
I really hope you can help.
Thank you
 
Last edited:
Looks like the error was occurring when there was no qualifying second highest score, this should catch it.

PHP:
=IFERROR(H2>(1/(1/MAX(IF(SIGN(C2:F2-B2:E2)<SIGN(D2:G2-C2:F2),D2:G2)))),FALSE)
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sorry I'm not following you

Example 1
39-37-70-38-24-27-36

Second highest, ignoring 38, is 27 AND 36 is greater than 27. Why result should be FALSE, since 36 is greater than 27?


Example 2
94-88-90-87-85-86-91

Second highest is 90 AND 91 is grater than 90.
My formula in #16
=IFERROR(H2>LARGE(IF(COLUMN(B2:G2)>MATCH(MAX(B2:G2),A2:G2,0)+1,IF(B2:G2>A2:F2,B2:G2)),1),FALSE)
returns TRUE as expected.

Could you clarify?

M.
Thank you Marcelo Branco and Jasonb75.
I made a small mistake.
i forgot to tell one important condition.
AFTER 2ND HIGHEST THERE SHOULD BE 1 OR 2 OR 3 DECREMENT VALUES.
For example
2-10-3-8-7-5-9
Here 8 is the 2nd highest.
after this we could see 7 and 5 which are decrement values.
Could you please modify based on this extra condition.Thank you for your effort.
 
Upvote 0
I'm confused...:confused:

Back to #1

Hello,
I have stumbled upon quite difficult task, maybe you could give me a hand at solving this task though.
SCORES OF STUDENTS
A1
SCORE
SCORE
SCORE
SCORE
SCORE
SCORE
SCORE
RESULT
ALAN
50
60
70
90
50
65
60
FALSE
BORIS
56
60
70
95
50
60
72
TRUE
CLIVE
60
62
90
80
40
60
70
TRUE
DENVER
50
100
90
70
60
80
77
FALSE
EZAN
60
95
90
80
50
51
75
TRUE

<TBODY>
</TBODY>


If H2 >2nd highest number result should be TRUE

1.If 2nd highest number is before 1st highest number ignore it and consider numbers which falls after 1st highest
(here Alan scored 70 marks which is 2nd highest. but it falls before 1st high = 90. consider 65 as 2nd highest and ignore 70 which was before 1st high score)
2.If 2nd highest falls immediately after 1st highest score, ignore that 2nd highest.
(Here Denver scored 90 which is 2nd highest but it falls immediately after 1st score. so consider 80 as 2nd highest)
3.If its a decrement value, dont consider any of the above values till we get an incremental value
(Here EZAN scred 95-90-80 but its in decremental series. so, 1st highest is 95 and 2nd highest is 51 and not 90 or 80)

4.Before 2nd highest number there should be a number which is lesser (Clive 2nd highest 70 . before that 60 which is lesser)
I really hope you can help.
Thank you

According this new criterion, what would be the 2nd highest for EZAN?

Please, clarify

M.
 
Upvote 0
I think this does it but haven't tested fully,

=IFERROR(H2>(1/(1/MAX((SIGN(C2:F2-B2:E2)<SIGN(D2:G2-C2:F2))*(D2:G2<MAX(B2:G2))*D2:G2))),FALSE)

Try using this variation for test purpose

=IFERROR((1/(1/MAX((SIGN(C2:F2-B2:E2)<SIGN(D2:G2-C2:F2))*(D2:G2<MAX(B2:G2))*D2:G2))),FALSE)

Omitting the first criteria will return the value of the second highest instead of the TRUE or FALSE result so it should be easier to confirm that the correct score is being evaluated.

The exception to this would be when there is no valid second highest score, this will still give a result of FALSE.
 
Upvote 0
I'm confused...:confused:

Back to #1



According this new criterion, what would be the 2nd highest for EZAN?

Please, clarify

M.
Here were i made one small mistake.
In my 1st post, i never thought this kindaa problem will arise.
let the new modified values for EZAN as follows
95-90-80-50-51-48-75-TRUE
 
Last edited:
Upvote 0
Thank you jasonb75.
i'm getting a message like...
Your formula is missing a parenthesis--) or (.check the formula, and then add the parenthesis in the appropriate place.
when i use this
=IFERROR(H2>(1/(1/MAX((SIGN(C2:F2-B2:E2) formula.


 
Upvote 0
Really enjoying these solution attempts. Relianceaaa, what is the impetus for these rules? Reliance ETQ work?
 
Upvote 0
My last post was missing more than parenthesis, I forgot to add php tags to the formula so the software that controls the forum cut a big piece off of it. Looking at it again, I've noticed that it still fails in some points. Will repost after correcting the error.
 
Upvote 0
Looking at your examples again, no person ever has the same score twice, changing one score in your last sample,

95-90-95-50-51-48-75 should we take the second instance of 95, or 51?

Similarly, once the correct second highest score is established, if we find that it is equal to the score in column H, should the result be TRUE or FALSE?
 
Upvote 0
Here were i made one small mistake.
In my 1st post, i never thought this kindaa problem will arise.
let the new modified values for EZAN as follows
95-90-80-50-51-48-75-TRUE

I'm lost - i cannot help you.

Good luck.

M.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top