Moving Percentage calculation

[Lee]

Hello,

Any suggestions on a formula to calculate percentage changes year-on-year? I
have:

---A---- ---B---

1. 20.0% ?
2. 20.0%
3. 35.0%
4. 15.0%
5. 10.0%

Sum of column A is 100%. I'd like to take 10% of A1 and add it to A2 with
the result in B2 and so on, the result eventually being that row 5 has the
highest percent and row 1 the lowest -but with the totals of the columns
always adding up to 100%.

This is meant to show the sales of products moving from one product over
time to another, with the total always being 100% of the sales volume.

Thanks !
Lee

 

[smartin]

Hello,

Any suggestions on a formula to calculate percentage changes year-on-year? I
have:

---A---- ---B---

1. 20.0% ?
2. 20.0%
3. 35.0%
4. 15.0%
5. 10.0%

Sum of column A is 100%. I'd like to take 10% of A1 and add it to A2 with
the result in B2 and so on, the result eventually being that row 5 has the
highest percent and row 1 the lowest -but with the totals of the columns
always adding up to 100%.

This is meant to show the sales of products moving from one product over
time to another, with the total always being 100% of the sales volume.

Thanks !
Lee

Here are two possible interpretations.

If you mean each product nips 10% of the previous product's share, then
this does that, but the results make no sense after a few iterations to
the right because #1s share goes negative:

B2 =A1-0.1*SUM(A1:A4)
B3 =0.1*A1+A2 (fill down)

OTOH, If you mean each product nips 10% of product #1, the results make
more sense, but the leader among products 2-5 can never change (i.e., #3
stays highest):

B2 =0.6*A1
B3 =0.1*A$1+A2 (fill down)