+ Reply to Thread
Results 1 to 3 of 3

Percentage of change calculation

Hybrid View

  1. #1
    Registered User
    Join Date
    06-30-2008
    Location
    Georgia
    MS-Off Ver
    2013
    Posts
    72

    Percentage of change calculation

    Please see the attached image for reference.

    I am trying to calculate the change in percentage between the 2 months. The formula work great in any other numbering situation except for when the 1st column is a 0 and the 2nd column has a number. In this situation it always returns an error. I tried different things to try to make it work but had no success, could someone help me to make this work for this scenario, while still keeping what they formula currently does.

    Thanks. Please let me know if you have any questions.
    Attached Images Attached Images
    Last edited by VBA Noob; 01-16-2009 at 03:56 PM.

  2. #2
    Registered User
    Join Date
    11-03-2008
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2010
    Posts
    9
    First,
    You are getting the #DIV/0! error because of the 0 in cell J82.
    Your formula checks for various occurences of 0 in cells J82 and K82 but does not include what to do if J82 is zero.

    Secondly, your L82/J82 calculation will not give you the % CHANGE, you should try (L82-J82)/J82
    EG with L82 = 3 and J82 = 2
    L82/J82 = 3/2 = 1.5 => 150%
    (L82-J82)/J82 = (3-2)/2 = 0.5 =>50%

  3. #3
    Registered User
    Join Date
    06-30-2008
    Location
    Georgia
    MS-Off Ver
    2013
    Posts
    72
    aussie_sox - thanks for the tip on the change percentage, i will try that, however, I am needing to revise that formula to make it account for when J82 (or any J cell) is 0.
    =IF(J82+K82=0,0,IF(AND(J82>0,K82=0),-1,L82/J82))
    EDIT:
    I beleive I have the solution...see below...thanks for your help aussie
    =IF(J131+K131=0,0,IF(AND(J131=0,K131>0),1,L131/J131))
    Last edited by maddog9486; 01-20-2009 at 05:32 PM. Reason: figured out solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1