Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    formula help (2003 SP1)

    Hi,

    I was wondering if someone could help me with a formula. I have the following formula
    =IF(ISERROR(((+D16-E16)/E16)*100),"",((+D16-E16)/E16*100))

    (it is calculating the percent difference between the value for the current month and the value for the previous month)

    currently D 16 is value of 2 and E 16 is a value of 0

    the formula is returning a blank. What I would like it to do is show that there was a 200 percent increase, but I am not sure how to fit that requirement into my formula.

    I would like something like IF E16=0 then D16*100

    any help would be appreciated.

    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help (2003 SP1)

    First, there is a parenthesis out of place in your formula. It should read:

    <pre>=IF(ISERROR(((+D16-E16)/E16)*100),"",((+D16-E16)/E16)*100)
    </pre>


    Second, what you want to do is not mathematically correct. There has not been a 200% increase. If there had been a 200% increase, then E16+200%(E16)=D16. If E16=0 and D16=2, then that would mean that 0+200%(0)=2 which evaluates to 0=2, which is obviously incorrect. In this case, the percent increase is mathematically undefined and in fact approaches infinity.

    If you want to do what you asked anyway, then the formula would be:

    <pre>=IF(E16=0,D16*100,((+D16-E16)/E16)*100)
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: formula help (2003 SP1)

    Thanks for the help.

    Guess I wasn't thinking straight, thanks for pointing out my error. I'll keep the formula just incase the people who want the report insist on having a percentage.

Posting Permissions

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