In my Google Sheet, I have all my test scores, which I have averages of, by using =AVERAGE(). Out of these averages, I can see which is the highest by using =MAX() and the lowest by using =MIN(). I would like to create a code for a cell that:

1. Whichever the highest percentage is, it outputs the subject value (the column next to it).
2. Then, I would like another code (which I think I could code) that prints "Highest Average: Subject | Percentage"

What could I do?

CodePudding user response：

use:

={"Highest Average: "&JOIN(" | ", INDEX(TEXT(SORT({B:B, A:A}, 2, 0), {"@", "#.00%"}), 1));
  "Lowest Average: "& JOIN(" | ", INDEX(TEXT(SORT({B:B, A:A}, 2, 1), {"@", "#.00%"}), 1))}

CodePudding user response：

I have already attempted Step 1, but I am getting blank values, even when the conditions are true.

=IF(LEFT(C151,2) = (LEFT (E157,2))="TRUE","TRUE"," ")

CodePudding user response：

Try

=query(A:B,"select * order by A desc limit 1 ",0)

or

="Highest Average: "& query(A:B,"select B order by A desc limit 1 ",0)  &" | Percentage " &text(query(A:B,"select A order by A desc limit 1 ",0),"0.00%")

and

=query(A2:B,"select * where A is not null order by A asc limit 1 ",0)

or

="Lowest Average: "&
query(A2:B,"select B where A is not null order by A asc limit 1 ",0)  &
" | Percentage " &
text(query(A2:B,"select A where A is not null order by A asc limit 1 ",0),"0.00%")