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%
smuralisankarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
i dont see a question here, what is basically required
0
smuralisankarAuthor Commented:
I would like to know how this linear decay model is implemented in excel.
0
SteveCommented:
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
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

smuralisankarAuthor 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
SteveCommented:
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
ProfessorJimJamMicrosoft 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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.