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:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
What number is to be chosen if all numbers after the peak are decremental?
 

relianceaaa

Board Regular
Joined
Jan 24, 2014
Messages
76
Result should be false.
3.If its a decrement value, dont consider any of the above values till we get an incremental value
(Here EZAN scored 95-90-80 but its in decremental series. so, 1st highest is 95 and 2nd highest is 51 and not 90 or 80)
if its fully a decremental set of value result should be false
In EZAN score, 3 decremental follow by one increase.

 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
This works on your example data:
Code:
=LARGE(IF(B2:H2 < A2:G2,"",B2:H2),2)=H2
confirmed with CTRL+SHIFT+ENTER
 

relianceaaa

Board Regular
Joined
Jan 24, 2014
Messages
76

ADVERTISEMENT

Thank you BJUNGHEIM
Its not working correctly.
For a new set of numbers
80,63,54,49,59,56,52 i'm getting #NUM! error
19,14,17,22,32,28,29
I am getting TRUE.
32 = 1st
29 =value which we are going to check
how this can be TRUE?
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
Thank you BJUNGHEIM
Its not working correctly.
For a new set of numbers
80,63,54,49,59,56,52 i'm getting #NUM! error
19,14,17,22,32,28,29
I am getting TRUE.
32 = 1st
29 =value which we are going to check
how this can be TRUE?
For the first set, I'm not getting an error, but I took a closer look and if the scores are in order decending it will return a #NUM! error.

For the second, I mis-read your original criteria, try this:
Code:
=IFERROR(LARGE(IF(B6:G6 < A6:F6,"",B6:G6),2)<H6,FALSE)
(again entered with CTRL+SHIFT+ENTER)

EDIT: This won't ignore 2nd highest figures preceding a high score...more research is needed.
 

relianceaaa

Board Regular
Joined
Jan 24, 2014
Messages
76

ADVERTISEMENT

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.
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
Sorry, forum code cut off the end of my formula:
Code:
=IFERROR(LARGE(IF(B7:G7 < A7:F7,0,B7:G7),2) < H7,FALSE)
 

relianceaaa

Board Regular
Joined
Jan 24, 2014
Messages
76
for continuous decrease in scores or increase in scores, answer is TRUE.
(1,2,3,4,5,6,7
or
100,90,80,70,60)
moreover in many places its giving wrong answers.
can we easily fix it?
 

BJungheim

Well-known Member
Joined
Jul 24, 2004
Messages
914
The more I look at it the more I believe that it cannot be done in a single formula...at best I think you'd need 6 helper columns...let me know if that's a direction you're willing to go and I'll take a look.
 

Forum statistics

Threads
1,140,925
Messages
5,703,202
Members
421,280
Latest member
Jaycee01

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
Top