Search code examples
excelmathpercentagedecrement

Decrementing numbers based on total percent change to reach a desired average?

I'm trying to decrement numbers across 12 months based on total percent change but keep the average of the numbers consistent. I've tried a few different approaches that have not resulted in what I'm looking for. This is driving me crazy and I would really appreciate some help!

I've attached below image as example. My starting number in Jan is 26. My average target for the year is 20. Originally I had it so that I was equally dividing the numbers across the remaining months but I instead wanted a decrimental change. (ex. 26 -> 25 -> 24, etc until total average equals target of 20)

Original Start

Desired Change

What I've tried:

  • Calculated total percent change from starting number of 26 to 20 (23% decrease)
  • Dividing this by the remaining months (23%/11 = 2.098%)
  • Using this to get % decrease by month, which is obviously not the right approach, image below of what I've attempted:

Attempted Try

Solution

  • Intuitively you'd need to pair up the starting number (26) with 14, next number 23 with 15 etc. in each case making a total of 40 so the average of the two numbers is 20 so the average of the total must also be 20. However it doesn't quite work:

    26 14
25 15
24 16
23 17
22 18
21 19

    Oops - there is no month with 20 in it so it isn't an unbroken sequence. But -1 is a pretty good estimate of the decrement.

    More formally, if you are looking for a constant decrement, you could use the standard formula for an arithmetic progression and work out what the exact decrement would have to be:

     Sn = n/2[2a + (n − 1) × d]

    where a is the starting value (26), n is the number of terms (12) and d, the decrement is unknown.

    But this must be equal to the number of terms (12) * the required average (20) so you have

    n/2[2a + (n − 1) × d] = 20n

[2a + (n − 1) × d] = 40

52 + 11 × d = 40

11 × d = -12

d = -12/11

= -1.0909...

    If you wanted to achieve the same average by multiplying each month's value by a constant percentage then it would be a geometric progression and you could do a similar calculation using the appropriate formula.

    OK here is the GP approach. Here we have:

    Sn = a(1 - r^n) / (1 - r)

    This must be equal to 12*20 as before, and we have a=26 and n=12 which gives us

    26(1-r^12) / (1-r) = 240

1-r^12 / (1-r)= 240/26

    Let's call the right-hand side k for convenience and we end up with

    kr-r^12=k-1

    A solution doesn't exactly leap out at me but the solver gives

    r = 0.950799904605293

    If we plug these values in we get

    enter image description here

    It turns out in the linear case that we could have just gone with our gut that the first and last numbers should be 26 and 14, then divide the range from 14 to 26 into 11 equal intervals.