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>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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:
Upvote 0
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​
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top