Apr 22 2018 03:18 PM
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!
Apr 22 2018 04:38 PM
SolutionChris,
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
Apr 22 2018 08:38 PM
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.
Apr 22 2018 10:45 PM
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)
Apr 25 2018 10:24 PM
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).
Apr 25 2018 11:46 PM
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.
Apr 26 2018 01:38 PM
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
Apr 26 2018 02:38 PM
Macros are not my field of expertise.
Apr 26 2018 02:44 PM
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.
Apr 22 2018 04:38 PM
SolutionChris,
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