I have a Google Sheet with many different, separate tables starting in different rows. I want to find the last and second-last nonblank value in a particular column inside one specific table.
The nuance here is my table starts in row 89 and ends in row 141. I have other tables both above and below it sharing the same columns E and F.
For the purposes of my sample table I'm just going to include a smaller number of rows.
| Date | Value |
|---|---|
| 1/7/2024 | 3 |
| 1/14/2024 | 10 |
| 1/21/2024 | 11 |
| 1/28/2024 | 14 |
| 2/4/2024 | 3 |
| 2/11/2024 | |
| 2/18/2024 | |
| 2/25/2024 | |
| 3/3/2024 | |
| 3/10/2024 | |
| 3/17/2024 | |
| 3/24/2024 | |
| 3/31/2024 | |
| 4/7/2024 | |
| 4/14/2024 | |
| 4/21/2024 | |
| 4/28/2024 | |
| 5/5/2024 | |
| 5/12/2024 | |
| 5/19/2024 | |
| 5/26/2024 |
In a different cell in my Google Sheet, I want it to populate with 3 (associated with date 2/4/2024). In another cell I want it to populate with 14 (associated with date 1/28/2024). These numbers correspond to the last and second last non-blank values in the "Value" column, respectively. The "Value" column is located in column F while the "Date" column is located in column E of my Google Sheet.
Below are the formulas I tried:
- Last (Non-Blank) Value:
=filter(F90:F110,E90:E110=large(E90:E110,1)) - Second-Last (Non-Blank) Value:
=filter(F90:F111,E90:E111=large(E90:E111,2))
But both formulas return blanks because they consider dates associated with blank values in "Value" column.
How can I fix my formulas to get my desired outputs?
You may try this for last
non-blank(date_column is not taken into account):-1to-2for second-last