Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 38

Thread: 2nd highest score analysis using vlookup or macros

  1. #1
    Board Regular relianceaaa's Avatar
    Join Date
    Jan 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 2nd highest score analysis using vlookup or macros

    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

    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 by relianceaaa; Jun 5th, 2014 at 01:33 PM.

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    What number is to be chosen if all numbers after the peak are decremental?
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  3. #3
    Board Regular relianceaaa's Avatar
    Join Date
    Jan 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    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.


  4. #4
    Board Regular
    Join Date
    Jul 2004
    Location
    Flordia
    Posts
    914
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    This works on your example data:
    Code:
    =LARGE(IF(B2:H2 < A2:G2,"",B2:H2),2)=H2
    confirmed with CTRL+SHIFT+ENTER

  5. #5
    Board Regular relianceaaa's Avatar
    Join Date
    Jan 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    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?

  6. #6
    Board Regular
    Join Date
    Jul 2004
    Location
    Flordia
    Posts
    914
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    Quote Originally Posted by relianceaaa View Post
    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)
    
    (again entered with CTRL+SHIFT+ENTER)

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

  7. #7
    Board Regular relianceaaa's Avatar
    Join Date
    Jan 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    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.

  8. #8
    Board Regular
    Join Date
    Jul 2004
    Location
    Flordia
    Posts
    914
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    Sorry, forum code cut off the end of my formula:
    Code:
    =IFERROR(LARGE(IF(B7:G7 < A7:F7,0,B7:G7),2) < H7,FALSE)

  9. #9
    Board Regular relianceaaa's Avatar
    Join Date
    Jan 2014
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    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?

  10. #10
    Board Regular
    Join Date
    Jul 2004
    Location
    Flordia
    Posts
    914
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd highest score analysis using vlookup or macros

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •