Hello!
I am trying to populate missing values based on historical data. The issue is that not every historical year contains data for every variable. So, what I need is a formula to pull the maximum (non-zero) value for a variable from the most recent year that contains data.
Example historical data can be seen below...
<tbody>
</tbody>
Based on the above table, I would want to return the following results for variables A and B for each item X and Y...
<tbody>
</tbody>
Appreciate any and all help!!
I am trying to populate missing values based on historical data. The issue is that not every historical year contains data for every variable. So, what I need is a formula to pull the maximum (non-zero) value for a variable from the most recent year that contains data.
Example historical data can be seen below...
Item | Year | A | B |
X | 2014 | 330 | 4325 |
X | 2014 | 250 | 4500 |
X | 2015 | 290 | 0 |
X | 2015 | 330 | 0 |
X | 2016 | 0 | 0 |
X | 2016 | 400 | 0 |
Y | 2015 | 0 | 3200 |
Y | 2015 | 230 | 4000 |
Y | 2016 | 200 | 0 |
Y | 2016 | 175 | 0 |
<tbody>
</tbody>
Based on the above table, I would want to return the following results for variables A and B for each item X and Y...
Item | A | B |
X | 400 | 4500 |
Y | 200 | 4000 |
<tbody>
</tbody>
Appreciate any and all help!!