I have a table of raw grape analysis results, for which I need to create a summary table that includes a calculated harvest date.
In order to calculate the harvest date, I need to know the current speed of ripening. To calculate the speed of ripening, I need to know the most recent analysis and the second-most-recent analysis. To do this, I need Excel to find within the instances of "FIO" (for example) the Baume from the most recent analysis (and subsequently the second-most-recent analysis).
I can work out how to get the most recent date with a MAX function, but in this case, that would return a date of 6th-Feb, which is not valid as there is no FIO analysis for 6-Feb.
How do I return the Baume where Sample = "FIO" and Date is the last date of any FIO sample?
In this case the Date would be 3rd Feb and the resultant Baume 12.65
Date | Sample | Baumé | pH | TA |
29/1/25 | SHZ | 11.65 | 3.11 | 8.1 |
29/1/25 | FIO | 10.6 | 2.86 | 14.3 |
29/1/25 | VER | 9.75 | 2.98 | 10.5 |
29/1/25 | GRN | 11.5 | 2.96 | 9.1 |
29/1/25 | CAS | 11.8 | 3.1 | 9.3 |
3/2/25 | SHZ | 13.55 | 3.23 | 7.3 |
3/2/25 | FIO | 12.65 | 2.97 | 10.6 |
3/2/25 | VER | 11.15 | 3.13 | 7.9 |
3/2/25 | GRN | 12.85 | 3.08 | 7.9 |
3/2/25 | CAS | 12.6 | 3.19 | 7.7 |
6/2/25 | CAF | 12.53 | 3.14 | 7 |
6/2/25 | SHZ | 13.7 | 3.21 | 5.9 |
In order to calculate the harvest date, I need to know the current speed of ripening. To calculate the speed of ripening, I need to know the most recent analysis and the second-most-recent analysis. To do this, I need Excel to find within the instances of "FIO" (for example) the Baume from the most recent analysis (and subsequently the second-most-recent analysis).
I can work out how to get the most recent date with a MAX function, but in this case, that would return a date of 6th-Feb, which is not valid as there is no FIO analysis for 6-Feb.
How do I return the Baume where Sample = "FIO" and Date is the last date of any FIO sample?
In this case the Date would be 3rd Feb and the resultant Baume 12.65