+ Reply to Thread
Results 1 to 4 of 4

IF Statement problem

  1. #1
    Registered User
    Join Date
    07-28-2005
    Posts
    8

    IF Statement problem

    I have a problem with my IF statement it wont stop at the correct answer for example

    I have IO = 20 000
    Y1 cash flow = 5 000
    Y2 cash flow = 5 000
    Y3 cash flow = 10 000
    Y4 cash flow = 25 000

    Im using the following IF statement

    =IF(5000>20000,1+(20000-5000)/20000,
    IF(5000+5000>20000,2+((20000-5000)-5000)/20000,
    IF(5000+5000+10000>20000,3+(((20000-5000)-5000)-5000)/20000,
    IF(5000+5000+10000+25000>20000,4+((((20000-5000)-5000)-5000)-25000)/20000))))

    I rewrote my equation so the numbers make sense but i am using cell references so if the IO or cash flow changes the answer will change too.
    Using my equation the answer should be 3 but it moves to the next true if statement.

    My question is how do i stop the equation from moving to the next true if statement. The equations are correct but im having trouble stopping the equations at the correct answer. In this case being the third if statement.

  2. #2
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    Quote Originally Posted by trixma
    I have a problem with my IF statement it wont stop at the correct answer for example

    I have IO = 20 000
    Y1 cash flow = 5 000
    Y2 cash flow = 5 000
    Y3 cash flow = 10 000
    Y4 cash flow = 25 000

    Im using the following IF statement

    =IF(5000>20000,1+(20000-5000)/20000,
    IF(5000+5000>20000,2+((20000-5000)-5000)/20000,
    IF(5000+5000+10000>20000,3+(((20000-5000)-5000)-5000)/20000,
    IF(5000+5000+10000+25000>20000,4+((((20000-5000)-5000)-5000)-25000)/20000))))

    I rewrote my equation so the numbers make sense but i am using cell references so if the IO or cash flow changes the answer will change too.
    Using my equation the answer should be 3 but it moves to the next true if statement.

    My question is how do i stop the equation from moving to the next true if statement. The equations are correct but im having trouble stopping the equations at the correct answer. In this case being the third if statement.

    =IF(5000>20000,1+(20000-5000)/20000,
    IF(5000+5000>20000,2+((20000-5000)-5000)/20000,
    IF(5000+5000+10000>20000,3+(((20000-5000)-5000)-5000)/20000,
    IF(5000+5000+10000+25000>20000,4+((((20000-5000)-5000)-5000)-25000)/20000))))

    All the relationships you have established are inequalities. You should provide for the possibility of the two factors being equal, as well.

    Your formula can be modified by changing the red portion highlighted above to the blue highlighted portion shown below. Furthermore, you can get rid of some parentheses as well.

    =IF(5000>=20000,1+(20000-5000)/20000,
    IF(5000+5000>=20000,2+(20000-5000-5000)/20000,
    IF(5000+5000+10000>=20000,3+(20000-5000-5000-5000)/20000,4+(20000-5000-5000-5000-25000)/20000))))

    Hope this is what you need.

    Regards.
    BenjieLop
    Houston, TX

  3. #3
    Rowan
    Guest

    Re: IF Statement problem

    Your 3rd if statement checks whether 5000+5000+10000 > 20000.
    5000+5000+10000 is not greater than 20000 it is equal to 20000 so if you
    want this to evaluate to true this should change to 5000+5000+10000 >=
    20000.

    Hope this helps
    Rowan

    trixma wrote:
    > I have a problem with my IF statement it wont stop at the correct answer
    > for example
    >
    > I have IO = 20 000
    > Y1 cash flow = 5 000
    > Y2 cash flow = 5 000
    > Y3 cash flow = 10 000
    > Y4 cash flow = 25 000
    >
    > Im using the following IF statement
    >
    > =IF(5000>20000,1+(20000-5000)/20000,
    > IF(5000+5000>20000,2+((20000-5000)-5000)/20000,
    > IF(5000+5000+10000>20000,3+(((20000-5000)-5000)-5000)/20000,
    > IF(5000+5000+10000+25000>20000,4+((((20000-5000)-5000)-5000)-25000)/20000))))
    >
    > I rewrote my equation so the numbers make sense but i am using cell
    > references so if the IO or cash flow changes the answer will change
    > too.
    > Using my equation the answer should be 3 but it moves to the next true
    > if statement.
    >
    > My question is how do i stop the equation from moving to the next true
    > if statement. The equations are correct but im having trouble stopping
    > the equations at the correct answer. In this case being the third if
    > statement.
    >
    >


  4. #4
    Registered User
    Join Date
    07-28-2005
    Posts
    8
    Thanks that worked

+ 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