% increase.

[thenorthernmonkey]

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:

[TABLE="width: 152"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 321"]
<tbody>[TR]
[TD="align: center"]Site Name[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]% variance[/TD]
[/TR]
[TR]
[TD="align: center"]Site 1[/TD]
[TD="align: center"]11.24[/TD]
[TD="align: center"]107.70[/TD]
[TD="align: center"]858%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 2[/TD]
[TD="align: center"]32.51[/TD]
[TD="align: center"]362.98[/TD]
[TD="align: center"]1017%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 3[/TD]
[TD="align: center"]55.36[/TD]
[TD="align: center"]443.15[/TD]
[TD="align: center"]700%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 4[/TD]
[TD="align: center"]217.80[/TD]
[TD="align: center"]530.21[/TD]
[TD="align: center"]143%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 5[/TD]
[TD="align: center"]170.16[/TD]
[TD="align: center"]430.05[/TD]
[TD="align: center"]153%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 6[/TD]
[TD="align: center"]123.39[/TD]
[TD="align: center"]904.87[/TD]
[TD="align: center"]633%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 7[/TD]
[TD="align: center"]34.99[/TD]
[TD="align: center"]127.77[/TD]
[TD="align: center"]265%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 8[/TD]
[TD="align: center"]38.55[/TD]
[TD="align: center"]407.42[/TD]
[TD="align: center"]957%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 9[/TD]
[TD="align: center"]24.66[/TD]
[TD="align: center"]92.61[/TD]
[TD="align: center"]276%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 10[/TD]
[TD="align: center"]552.05[/TD]
[TD="align: center"]4847.96[/TD]
[TD="align: center"]778%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 11[/TD]
[TD="align: center"]428.42[/TD]
[TD="align: center"]1248.95[/TD]
[TD="align: center"]192%[/TD]
[/TR]
[TR]
[TD="align: center"]Site 12[/TD]
[TD="align: center"]6.06[/TD]
[TD="align: center"]28.57[/TD]
[TD="align: center"]372%

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

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]

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]

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

 

[shg]

One way, perhaps:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Site​

[/td][td="bgcolor:#F3F3F3"]

Jan​

[/td][td="bgcolor:#F3F3F3"]

Feb​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

12​

[/td][td]

6.06​

[/td][td]

28.57​

[/td][td="bgcolor:#63BE7B"]

3.38​

[/td][td]D2: =LOG(C2/B2 * (C2-B2)^2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

9​

[/td][td]

24.66​

[/td][td]

92.61​

[/td][td="bgcolor:#9CCE7E"]

4.24​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

7​

[/td][td]

34.99​

[/td][td]

127.77​

[/td][td="bgcolor:#ADD37F"]

4.50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1​

[/td][td]

11.24​

[/td][td]

107.70​

[/td][td="bgcolor:#CBDC81"]

4.95​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

5​

[/td][td]

170.16​

[/td][td]

430.05​

[/td][td="bgcolor:#DEE182"]

5.23​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

4​

[/td][td]

217.80​

[/td][td]

530.21​

[/td][td="bgcolor:#E7E482"]

5.38​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

3​

[/td][td]

55.36​

[/td][td]

443.15​

[/td][td="bgcolor:#FFD981"]

6.08​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

2​

[/td][td]

32.51​

[/td][td]

362.98​

[/td][td="bgcolor:#FED981"]

6.09​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

8​

[/td][td]

38.55​

[/td][td]

407.42​

[/td][td="bgcolor:#FED580"]

6.16​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

11​

[/td][td]

428.42​

[/td][td]

1248.95​

[/td][td="bgcolor:#FECE7F"]

6.29​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

6​

[/td][td]

123.39​

[/td][td]

904.87​

[/td][td="bgcolor:#FDBB7B"]

6.65​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

10​

[/td][td]

552.05​

[/td][td]

4847.96​

[/td][td="bgcolor:#F8696B"]

8.21​

[/td][td][/td][/tr]
[/table]

 

[shg]

Or ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Site​

[/td][td="bgcolor:#F3F3F3"]

Jan​

[/td][td="bgcolor:#F3F3F3"]

Feb​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

12​

[/td][td]

6.06​

[/td][td]

28.57​

[/td][td="bgcolor:#63BE7B"]

0.3%​

[/td][td]D2: =(C2 - B2) / C$14[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

9​

[/td][td]

24.66​

[/td][td]

92.61​

[/td][td="bgcolor:#7AC47C"]

0.9%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

7​

[/td][td]

34.99​

[/td][td]

127.77​

[/td][td="bgcolor:#87C87D"]

1.2%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1​

[/td][td]

11.24​

[/td][td]

107.70​

[/td][td="bgcolor:#89C97D"]

1.2%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

5​

[/td][td]

170.16​

[/td][td]

430.05​

[/td][td="bgcolor:#DEE182"]

3.3%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

4​

[/td][td]

217.80​

[/td][td]

530.21​

[/td][td="bgcolor:#FAE983"]

4.0%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

2​

[/td][td]

32.51​

[/td][td]

362.98​

[/td][td="bgcolor:#FFEB84"]

4.2%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

8​

[/td][td]

38.55​

[/td][td]

407.42​

[/td][td="bgcolor:#FFEA84"]

4.7%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

3​

[/td][td]

55.36​

[/td][td]

443.15​

[/td][td="bgcolor:#FFE984"]

4.9%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

6​

[/td][td]

123.39​

[/td][td]

904.87​

[/td][td="bgcolor:#FFDC82"]

10.0%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

11​

[/td][td]

428.42​

[/td][td]

1248.95​

[/td][td="bgcolor:#FFDB81"]

10.5%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

10​

[/td][td]

552.05​

[/td][td]

4847.96​

[/td][td="bgcolor:#F8696B"]

54.8%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td][/td][td="bgcolor:#E5E5E5"]

7837.05​

[/td][td][/td][td]C14: =SUM(C2:C13) - SUM(B2:B13)[/td][/tr]
[/table]