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?
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.