Forum Discussion
Spreading a value over preceding and following cell or TRUE value when IF criteria met
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
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
Detlef, you Sir are a gentleman and a scholar. Your solution worked perfectly.
It there any way this can be automated for the {3;2;1}? I tried trying to call in a cell value but it went hideously wrong.
Expand the table B20:C22 to B20:F22 and add the following formulas/values:
=B20
=B20-1
13 300 3 2 1 5 500 5 4 1 7 700 7 6 1 The final formula in C4 will be:
=IF(C$3<$B4-1,0,SUMPRODUCT((MOD(C$3-1,$B4)+1=VLOOKUP($B4,$B$20:$F$22,{3;4;5},FALSE))*{0.6;0.2;0.2}))*VLOOKUP($B4,$B$20:$F$22,2,FALSE)Fantastic!
Is there a quick way of changing the year spread? I tried adding components in the same format to achieve a '5-year' spread, it kept the initial spread (3-years) for the first occurrence, and then it went to different years without maintaining a central position over year five (+/- 2-years).
