# How to implement a linear decay model in excel

Let's just say that there are 5 interactions.

The 4 interactions that came before the 5th are all channels. The last (5th) one is a direct interaction.

The weights are distributed equally - so 20% to each interaction.

The weight of the last direct interaction (20%) is taken and distributed to the ones before it.

For ex:
Interaction 1:
20%

Interaction 2:
20%

Interaction 3:
20%

Interaction 4:
20%

Interaction 5:
20%

Then, The final result would be:
Interaction 1:
21.5%

Interaction 2:
23.5%

Interaction 3:
25%

Interaction 4:
30%

Interaction 5:
20%

We got there by doing this:
Interaction 1:
20%
+ 1.5%

Interaction 2:
20%
+ 3.5%

Interaction 3:
20%
+ 5%

Interaction 4:
20%
+ 10%

Interaction 5:
20%
- 20%
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft Excel ExpertCommented:
i dont see a question here, what is basically required
0
Author Commented:
I would like to know how this linear decay model is implemented in excel.
0
Commented:
I do not see a 'linear decay' in your example as the 3.5 is not half of the number before (as the 10 and 5 are).

There are two simple posibilities:

1)
1      20%      2%      22%
2      20%      4%      24%
3      20%      6%      26%
4      20%      8%      28%
5      20%      -20%      0%

or 2)
1      20%      1.3%      21.3%
2      20%      2.5%      22.5%
3      20%      5.0%      25.0%
4      20%      10.0%      30.0%
5      20%      -20.0%      0.0%

But number 2 doesn't resolve to zero.

So could you explain further, or explain the differences.
U--Examples.xlsx
0
Author Commented:
Yes. It is not linear since there were no more interactions left to distribute it to. But I did not want to give the last two equal weights. Which is why instead of 2.5 and 2.5, I gave it as 3.5 and 1.5.
0
Commented:
You could use GOAL SEEK to derive a divisor to give a redcution model that does go to zero.

See the attached workbook for example.
This gives the below result:

1      20%      1.5%      21.5%
2      20%      2.8%      22.8%
3      20%      5.4%      25.4%
4      20%      10.4%      30.4%
5      20%      -20.0%      0.0%

Remainder      0.06%
Divisor       1.924
U--Examples.xlsx
0
Microsoft Excel ExpertCommented:
Steve,

the remainder should be zero, so it shows 0.06% .    to get it down precisely to zero.  Solver Add-in can do that.
so the divisor will change to  1.92756197552722
0

Experts Exchange Solution brought to you by