# Thread: % increase. Thanks:  1 Post #5323064 (1) Likes:  2 Post #5323064 (1)Post #5323108 (1)

1. ## % increase.

Hi all,

New user and couldn't seem to find exactly what I was after, so hopefully below makes some sense.

Context:
I'm looking at utility spend across our portfolio of around 100 locations, each location is a different size and spend is location specific.
What I'm trying to pull out is any location with a significant change in cost month on month, but a simple % variance isn't quite working due to the location specific element, and just using cost doesn't take into account the scale of expected cost.

Example:

Site 1 Jan: £100
Site 1 Feb: £150
% Increase: 50%
Cost increase: £50

Site 2 Jan: £1,000,000
Site 2 Feb: £1,200,000
% Increase: 20%
Cost Increase: £200,000

So I'm more interested in a 20% increase on site 2 as it relates to a higher cost value.
Formula used to get the % change on a location by location basis is =(current month - previous month) / previous month

Is there a formula that would highlight significant cost changes, but also show the scale / impact against the rest of the portfolio?
ie - I want to easily see the £200k increase.

Quick sample set below, ones in bold are the have the biggest month on month location specific cost increase, but aren't the biggest % increase:

 Site Name Jan Feb % variance Site 1 11.24 107.70 858% Site 2 32.51 362.98 1017% Site 3 55.36 443.15 700% Site 4 217.80 530.21 143% Site 5 170.16 430.05 153% Site 6 123.39 904.87 633% Site 7 34.99 127.77 265% Site 8 38.55 407.42 957% Site 9 24.66 92.61 276% Site 10 552.05 4847.96 778% Site 11 428.42 1248.95 192% Site 12 6.06 28.57 372%

2. ## Re: % increase.

This isn't really an Excel question is it, it's a maths question or maybe even an accounting policy question.

If absoloute variance in spend is important to you, why not just do that - focus on largest absolute variance ?

On the other hand, IF there are some sites where absolute variance is most significant, and other sites where %age variance is most significant, what are the criteria that you would use to make that kind of assessment ?
If you can express that in clear rules, you might be able to build an Excel formula that can replicate it.

3. ## Re: % increase.

Thanks for the reply!

Definitely more of a maths query, but I'll have a re-think on how to describe what I'm trying to do.

4. ## Re: % increase.

could you not just subtract?
B - C
and then create conditional format of if E < -1000 (or whatever value you want) highlight cells.

5. ## Re: % increase.

One way, perhaps:

 A B C D E 1 Site Jan Feb 2 12 6.06 28.57 3.38 D2: =LOG(C2/B2 * (C2-B2)^2) 3 9 24.66 92.61 4.24 4 7 34.99 127.77 4.50 5 1 11.24 107.70 4.95 6 5 170.16 430.05 5.23 7 4 217.80 530.21 5.38 8 3 55.36 443.15 6.08 9 2 32.51 362.98 6.09 10 8 38.55 407.42 6.16 11 11 428.42 1248.95 6.29 12 6 123.39 904.87 6.65 13 10 552.05 4847.96 8.21

6. ## Re: % increase.

Or ...

 A B C D E 1 Site Jan Feb 2 12 6.06 28.57 0.3% D2: =(C2 - B2) / C\$14 3 9 24.66 92.61 0.9% 4 7 34.99 127.77 1.2% 5 1 11.24 107.70 1.2% 6 5 170.16 430.05 3.3% 7 4 217.80 530.21 4.0% 8 2 32.51 362.98 4.2% 9 8 38.55 407.42 4.7% 10 3 55.36 443.15 4.9% 11 6 123.39 904.87 10.0% 12 11 428.42 1248.95 10.5% 13 10 552.05 4847.96 54.8% 14 Total 7837.05 C14: =SUM(C2:C13) - SUM(B2:B13)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•