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

Money Making Options

Post meta

This entry was written by Sandi Mays and posted on March 26, 2009 at 12:17 am and filed under Definitions, Excel. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment(Latest is displayed first) or leave a trackback: Trackback URL.

Share with others

Post Formula to calculate growth - with positive and negative numbers to digg. Post Formula to calculate growth - with positive and negative numbers to Reddit add to stumbleupon Google Bookmark myweb bookmark on del.cio.us newsvine Tailrank magnolia Furl co.mments shadows simpy blinklist

3 Comments so far (Add 1 more)

  1. 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

    1. Jonathan on July 12th, 2010 at 6:38 am

  2. The first explanation worked for me

    2. Miss Tee on January 14th, 2010 at 5:15 am

  3. 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…

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

Post a Comment

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

*
*