Results 1 to 6 of 6

Thread: % increase.

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default % 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. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    460
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by BlakeSkate; Aug 9th, 2019 at 11:31 AM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,610
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default 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. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,610
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default 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)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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