Forum Discussion
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 60% of it's TRUE value (TRUE value being 300). E3 to be 20% of E4's TRUE value and E5 to be 20% of E4's TRUE value. If the cells with the 20% values overlap, they need to SUM.
I am currently using an IF function to get the value in E4 so I am unable to use an additional formula within the cells, so I am looking for a Macros and VBA solution. I have tried several IF function modifications, all resulting in circular reference issues when the cells overlap.
The object of the Macros and VBA solution is to smooth out the data to provide a more realistic model for the life (top row being years) of a machine. The values (3, 5 and 7) are the frequency of occurrence through the life (Mean Time Between Failure, or MTBF) of each component, and the values being put in to the lifecycle table (300, 500 and 700) being the cost of component replacement.
Can anyone help me?
Thank you in advance!
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
8 Replies
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$21Detlef, 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)
