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
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
1
| 3 | 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).
The 3 year-spread is defined by D20:F22, by {3;4;5} (in VLOOKUP()) and by {0,6;0,2;0,2}.
Expand D20:F22 to D20:H22.
D21: =B21
E21: =B21-1
F21: 1
G21: =B21-2
H21: 2
Expand {3;4;5} to {3;4;5;6;7}.
Expand {0,6;0,2;0,2} to {0,5;0,2;0,2;0,05;0,05}.
And -1 has to be changed to -2.
=IF(C$3<$B4-2,0,SUMPRODUCT((MOD(C$3-1,$B4)+1=VLOOKUP($B4,$B$20:$H$22,{3;4;5;6;7},FALSE))*{0.5;0.2;0.2;0.05;0.05}))*VLOOKUP($B4,$B$20:$H$22,2,FALSE)But it will not work for your first assumption. You cannot spread 300 every 3 years over 5 years.
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