Google-sheets – get value left of max value

google sheetsgoogle-sheets-arrayformulagoogle-sheets-dates

I have a sheet that looks like:

Date         Value      Highest     Lowest
----------------------------------------------------------------
1-1-2020     23         26           20 
2-1-2020     20
3-1-2020     26
4-1-2020     22
5-1-2020     24

Where highest is the highest value in the value row =MAX(B2:B999) and lowest is the lowest value in the value row =MIN(B2:B999)
What I want is that below the highest value (26) I have the date of that value in this case 3 January. Also for the lowest I want to get the lowest date 2 January.

Best Answer

Please Place under Highest

=ARRAY_CONSTRAIN(SORT(A2:B7,2,0),1,1)

and under Lowest

=ARRAY_CONSTRAIN(SORT(A2:B7,2,1),1,1)

Adjust ranges accordingly.

Functions used:

Related Solutions

Google-sheets – Min/Max of a row only if value in other row is X

I believe this is what you're looking for:

=ARRAYFORMULA(min(if(B2:B6=5,A2:A6)))

The full documentation for arrayformula is at Google Support. "This function allows the specification of a range (or multiple ranges of the same size) in functions that don't normally accept ranges."

Google-sheets – How to get the name of the row which is the mode in Google Spreadsheets

For the highest value you can use:

=INDEX(sort(A:B, 2, false), 1, 1)

This first sorts and then retrieves the value from the first row and column of the sorted data.

Using the opposite sort order in the formula will yield the lowest value:

=INDEX(sort(A:B, 2, true), 1, 1)

Best Answer

Related Solutions

Google-sheets – Min/Max of a row only if value in other row is X

Google-sheets – How to get the name of the row which is the mode in Google Spreadsheets

Related Topic