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...
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Year[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]2014[/TD]
[TD]330[/TD]
[TD]4325[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2014[/TD]
[TD]250[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2015[/TD]
[TD]290[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2015[/TD]
[TD]330[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2016[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2016[/TD]
[TD]400[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2015[/TD]
[TD]0[/TD]
[TD]3200[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2016[/TD]
[TD]200[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2016[/TD]
[TD]175[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Based on the above table, I would want to return the following results for variables A and B for each item X and Y...
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]400[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]200[/TD]
[TD]4000[/TD]
[/TR]
</tbody>[/TABLE]
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...
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Year[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]2014[/TD]
[TD]330[/TD]
[TD]4325[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2014[/TD]
[TD]250[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2015[/TD]
[TD]290[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2015[/TD]
[TD]330[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2016[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2016[/TD]
[TD]400[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2015[/TD]
[TD]0[/TD]
[TD]3200[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2016[/TD]
[TD]200[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2016[/TD]
[TD]175[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Based on the above table, I would want to return the following results for variables A and B for each item X and Y...
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]400[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]200[/TD]
[TD]4000[/TD]
[/TR]
</tbody>[/TABLE]
Appreciate any and all help!!