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(500020000,1+(20000-5000)/20000,
IF(5000+500020000,2+((20000-5000)-5000)/20000,
IF(5000+5000+1000020000,3+(((20000-5000)-5000)-5000)/20000,
IF(5000+5000+10000+2500020000,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.


--
trixma
------------------------------------------------------------------------
trixma's Profile: http://www.excelforum.com/member.php...o&userid=25702
View this thread: http://www.excelforum.com/showthread...hreadid=471001

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(500020000,1+(20000-5000)/20000,
IF(5000+500020000,2+((20000-5000)-5000)/20000,
IF(5000+5000+1000020000,3+(((20000-5000)-5000)-5000)/20000,
IF(5000+5000+10000+2500020000,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(500020000,1+(20000-5000)/20000,
IF(5000+500020000,2+((20000-5000)-5000)/20000,
IF(5000+5000+1000020000,3+(((20000-5000)-5000)-5000)/20000,
IF(5000+5000+10000+2500020000,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


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=471001

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(500020000,1+(20000-5000)/20000,
IF(5000+500020000,2+((20000-5000)-5000)/20000,
IF(5000+5000+1000020000,3+(((20000-5000)-5000)-5000)/20000,
IF(5000+5000+10000+2500020000,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.

Thanks that worked


--
trixma
------------------------------------------------------------------------
trixma's Profile: http://www.excelforum.com/member.php...o&userid=25702
View this thread: http://www.excelforum.com/showthread...hreadid=471001