% increase.

thenorthernmonkey

New Member
Joined
Aug 9, 2019
Messages
4
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 NameJanFeb% variance
Site 111.24107.70858%
Site 232.51362.981017%
Site 355.36443.15700%
Site 4217.80530.21143%
Site 5170.16430.05153%
Site 6123.39904.87633%
Site 734.99127.77265%
Site 838.55407.42957%
Site 924.6692.61276%
Site 10552.054847.96778%
Site 11428.421248.95192%
Site 126.0628.57372%


<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
 

thenorthernmonkey

New Member
Joined
Aug 9, 2019
Messages
4
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.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
779
Office Version
2016
Platform
Windows
could you not just subtract?
B - C
and then create conditional format of if E < -1000 (or whatever value you want) highlight cells.
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
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​
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,566
Messages
5,469,443
Members
406,652
Latest member
LJA

This Week's Hot Topics

Top