Forum Discussion
Solved
Spreading a value over preceding and following cell or TRUE value when IF criteria met
I am making a cost model and I need to smooth out the data by distributing the value in percentages across the preceding and following cell; ie. E4 has a value of 300 - I want the value of E4 to be 6...
Chris,
either you described to wrongly or I understood it wrongly.
=IF(B$2<$A3-1,0,SUM((MOD(B$2-1,$A3)+1={3;2;1})*{0.6;0.2;0.2}))*$B$19
=IF(B$2<$A4-1,0,SUM((MOD(B$2-1,$A4)+1={5;4;1})*{0.6;0.2;0.2}))*$B$20
=IF(B$2<$A5-1,0,SUM((MOD(B$2-1,$A5)+1={7;6;1})*{0.6;0.2;0.2}))*$B$21
Hi Detlef,
Thank you again for your continued help. That is what I adjusted before, but I couldn't get that to work. I assumed I was doing something wrong when I kept getting cell values missing. I think I may need a different solution (Macros) as I needed to SUM the overlapping values.
Kind regards,
Chris
Macros are not my field of expertise.
No worries. I lay a 5-year average over the 3-year results, I am sticking with your original solution that works really well. I have put the 5-year spread in the "too-hard" basket.