+ Reply to Thread
Results 1 to 19 of 19

Find highest score and lowest points

  1. #1
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Find highest score and lowest points

    Hi,

    See attached document.

    In sheet 'data' i have a list of names. The names are displayed more than once. next to each name are numbers.

    In sheet 'results' column A is the list of names. In column B i want to find the highest score in relation to that name. In column C i want the lowest points in relation to the name and Column A.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    If you are still using Excel 2010:

    =MAX(IF(data!$A$2:$A$26=result!A2,data!$B$2:$B$26))

    =MIN(IF(data!$A$2:$A$26=result!A2,IF(data!$B$2:$B$26=B2,data!$C$2:$C$26)))

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files
    Last edited by AliGW; 10-29-2019 at 06:02 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find highest score and lowest points

    or non array:
    =AGGREGATE(14,6,data!$B$2:$B$26/(data!$A$2:$A$26=$A2),1)

    and

    =AGGREGATE(15,6,data!$C$2:$C$26/((data!$A$2:$A$26=$A2)*(data!$B$2:$B$26=result!B2)),1)

    I was a bit unclear bout your requirement for the second one. The formula above gives the expected answers, but it didn't quite tie in with your description.
    Last edited by Glenn Kennedy; 10-29-2019 at 06:11 AM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    The description in the workbook is clearer - I missed it to begin with!

  5. #5
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Find highest score and lowest points

    Thanks Glenn. This works, but can you please explain to me what the 14,6 and 15,6 mean? Just so i can understand it better.

    Thanks

  6. #6
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Find highest score and lowest points

    Also i just tried to use this in GoogleSheets, but have discovered the "AGGREGATE" function does not exist in googlesheets. Is there an alternative?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Find highest score and lowest points

    1. 15 and 14 are AGGREGATEs subfunctions for SMALL and LARGE.

    2. For Googlesheets, maybe Ali's suggestion at Post 2. I never use that profgramme so I don't know.

    3. If you want a formula for Googlesheets ONLY, post ONLY in the "other platforms" subforum. If you want something bi-functional - say so right from the start. ThenI will be able to say "This works for Excel, but I don't know about GS".


    4. You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    I have a feeling that Google Sheets has an array prefix (something like =array(...), I believe). This may be needed for my suggestion to work.

    I completely echo what Glenn has just said: post in the right section or declare dual-platform requirements at the outset.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    Thread moved to Google Sheets section.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Find highest score and lowest points

    The prefix that Ali refers to in Post#8 is arrayformula, so try it with her earlier formula from Post #2 like this:

    =arrayformula( MAX(IF(data!$A$2:$A$26=result!A2,data!$B$2:$B$26)) )

    Similar for the other formula.

    Hope this helps.

    Pete

  11. #11
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Find highest score and lowest points

    works perfectly....Thank you very much. Really appreciate it

  12. #12
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Find highest score and lowest points

    Pete, I tried to change the second formula to this, but i am not getting any luck: =arrayformula( MAX(IF(15,6,data!$C$2:$C$26/((data!$A$2:$A$26=$A2)*(data!$B$2:$B$26=result!B2)),1)

    Am i missing something?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    You are looking at the wrong post. I don't believe that Google Sheets has the AGGREGATE function. You need to adapt the two formulae I gave you in post #2:

    =MAX(IF(data!$A$2:$A$26=result!A2,data!$B$2:$B$26))

    =MIN(IF(data!$A$2:$A$26=result!A2,IF(data!$B$2:$B$26=B2,data!$C$2:$C$26)))

    So, try this for the MIN formula:

    =arrayformula(MIN(IF(data!$A$2:$A$26=result!A2,IF(data!$B$2:$B$26=B2,data!$C$2:$C$26))))
    Last edited by AliGW; 11-01-2019 at 05:55 AM.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    I think I must be invisible here - not one acknowledgement of the contributions I've made here despite everybody else getting a mention. At least the thread is marked as SOLVED.

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Find highest score and lowest points

    I mentioned you in Post #10 !!

    Pete

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    I didn't mean you, Pete - I meant the OP. I think he must have set me to IGNORE in his profile.

  17. #17
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Find highest score and lowest points

    FWIW if this is only for Google Sheets, it would be well worth while learning to use the FILTER function.
    Rory

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Find highest score and lowest points

    Quote Originally Posted by AliGW View Post
    ... not one acknowledgement of the contributions I've made here ...
    He didn't give any rep either - just "this doesn't work, can you suggest something else ?"

    Pete

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find highest score and lowest points

    I'm not bothered about rep, although that's always nice if it's offered. It's the lack of basic common courtesy towards me in this thread by completely ignoring my posts that I find offensive. Some people take the free help they get here completely for granted, unfortunately.

    Anyway, time to move on. There are other people to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF formula that will rank numbers highest to lowest and then score them
    By slock92 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2017, 07:13 PM
  2. [SOLVED] how to find the lowest to highest value and display the name who is the lowest and the hig
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-23-2015, 09:20 PM
  3. [SOLVED] Highest And Lowest Score Order
    By StickCity in forum Excel General
    Replies: 4
    Last Post: 11-12-2014, 11:22 AM
  4. [SOLVED] VBA to determine the lowest and the highest Score in the data Table
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2014, 11:10 AM
  5. [SOLVED]A formula to give a running points total and display highest score
    By Graham.S in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-12-2013, 05:39 PM
  6. [SOLVED] Finding the category with the highest/lowest score
    By Vemonstrocity in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-08-2012, 07:08 PM
  7. Replies: 2
    Last Post: 03-20-2012, 06:30 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1