Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I am looking for a formula I can put into Google Sheets that finds the 1st, 2nd, and 3rd largest values in column B, but only for rows that also contain the number 1 in column A. Problem is, column A does have some blank cells, which I can not fill in.
| A | B | |
|---|---|---|
| 1 | 1 | $1,000 |
| 2 | $2,000 | |
| 3 | 1 | $3,000 |
| 4 | 1 | $4,000 |
FILTER to capture only the values in Column B that have an associated number in Column A.LARGE with the filtered data to get the 1st, 2nd and 3rd biggest numberse.g.: This formula in cell E2
=Large(filter($B$2:$B$5,$A$2:$A$5<>""),D2) where cell D2 contains a value of 1
SAMPLE
I prefer to use a query and get all 3 results directly with:
=ifna(query(A2:B,"select B where A = 1 order by B desc limit 3"))
The SORTN function returns the first n items in a data set after performing a sort.
SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)
SORTN is applied to the array returned by a FILTER function.
=SORTN(FILTER(B:B,A:A=1),3,,1,0)
SORTN is applied to an array returned by a nested SORTN function.
=SORTN(SORTN(B:B,1,1,A:A,-1),3,,1,0)
