Spreading a value over preceding and following cell or TRUE value when IF criteria met

[Chris Buller]

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!

[#2 · Accepted solution]

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

[#3]

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.  

[#4]

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)

[#5]

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).

[#6]

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.

[#7]

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 

[#8]

Macros are not my field of expertise.

[#9]

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.