Share via

Select highest score from table

Anonymous
2023-06-27T10:38:50+00:00

Hi all

I have a table that has a column for each point score, e.g. 24 points, 25 points 26 points etc. For each row I have the number of students that achieved that point score. For example, in 2022 two students achieved a score of 27, one of 32 etc.

In the "Highest Score" column I want to pick out the highest score based on the data in the points score columns. Does anyone know how I can achieve that?

Year 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Highest Score
2017 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 39
2018 0 0 1 1 0 1 1 0 0 0 0 2 0 0 0 0 1 0 0 0 0 0 40
2019 2 1 0 0 1 1 0 2 0 2 1 0 1 0 0 0 3 1 2 0 0 0 42
2020 0 1 1 0 1 1 0 0 0 1 0 1 0 1 1 0 0 1 0 0 0 1 45
2021 0 0 1 0 1 1 0 1 0 1 1 0 0 1 0 0 0 1 0 0 1 0 44
2022 0 0 1 2 1 0 1 0 1 0 0 0 0 1 1 0 0 0 0 0 0 0 38

Thanks!

Microsoft 365 and Office | Excel | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2023-06-28T02:45:53+00:00

    Thank you.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-06-28T00:45:30+00:00

    Not bad either!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-06-28T00:38:40+00:00

    I'm a big fan of short and simple - nice formula!

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2023-06-27T23:41:34+00:00

    Hi,

    In cell X2, enter this formula and drag down

    =XLOOKUP(TRUE,B2:W2>0,$B$1:$W$1,,,-1)

    Hope this helps.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2023-06-27T10:43:10+00:00

    =MAX(FILTER($B$1:$W$1,B2:W2>0))

    Was this answer helpful?

    0 comments