headermask image

Business Tools Blog

Formula to calculate growth - with positive and negative numbers

We track Annualized Revenue, Growth and Cashflow generation on a quarter over quarter and year over year basis to determine the health of the business.  If your calculation has both positive and negative numbers, you will need to use the Absolute Value* function in Excel.

Example 1:

  • 1Q Revenue: $400K
  • 2Q Revenue: $410K
  • The formula is (410-400)/400 = .025 or 2.5% per quarter.  Annualized the growth is 2.5%*4= 10%
  • The excel formula is (1Q-2Q)/ABS(1Q)*4

Example 2:

  • 1Q EBITDA: ($100)K
  • 2Q EBITDA: $50K
  • The formula is (50 - (100))/100 = 1.5 or 150% per quarter.  Annualized the growth is 150%*4=600%
  • The excel formula is (1Q-2Q)/ABS(1Q)*4

Example 3:

  • 1Q EBITDA: $50K
  • 2Q EBITDA: ($100)K
  • The formula is ((100)-50)/100 = -1.5 or (150%) per quarter.  Annualized the growth is (150%)*4=(600%)
  • The excel formula is (1Q-2Q)/ABS(1Q)*4

Using the “ABS” or Absolute value formula in the denominator is necessary for Example 2 to work.

* Absolute value is its numerical value irrespective of its sign. So, for example, 400 is the absolute value of both 400 and (400)

If you liked my post, feel free to subscribe to my rss feeds

6 Comments so far (Add 1 more)

  1. Greetings I am so happy I found your website, I really found you by error, while I was looking on Bing for something else, Anyhow I am here now and would
    just like to say cheers for a fantastic post and a all
    round interesting blog (I also love the theme/design), I don’t have time to look over it all at the moment but I have bookmarked it and also included your RSS feeds, so when I
    have time I will be back to read a lot more, Please
    do keep up the great work.

    1. mudanzas on January 11th, 2013 at 2:09 am
  2. In perusing through some of the cost centers within the Talent Chaser Data Repository,I noticed that the Revenue and Expenses variances in some cases were positive and should have been reflected as a negative and vice versa. Is it possible for you to have someone quickly go through the cost centers to ensure this is correct as naturally it will cause the aggregate numbers to appear out of synch

    2. sonia on June 23rd, 2011 at 8:52 am
  3. What Eric says is true. But that doesn’t solve the problem.

    3. usws on October 23rd, 2010 at 11:33 pm
  4. Actually to show growth, the excel formula should be Q2 - Q1, not Q1 - Q2. Eric is correct, which raises the point of how meaningful it is to show growth in % when you move from negative to positive or vice versa. The WSJ just marks it as a P(profit) or L(loss) until the numbers are both positive.

    Jonathan

    4. Jonathan on July 12th, 2010 at 6:38 am
  5. The first explanation worked for me

    5. Miss Tee on January 14th, 2010 at 5:15 am
  6. This seems to work but it doesn’t. Try using a lower negative value in your Q1 and you will see that the % variance actually decreases the further you pull away rather than growing.

    In above mentioned example: Q1 = -100 and Q2 = 50 your output is: 150%. Now if it were -200 instead of -100 then the output becomes:
    125% which is ridiculous. It should be more not less…

    6. eric on June 2nd, 2009 at 6:06 am

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*