If statement nesting

**msbing916** · 05-07-2010, 01:28 PM

I am working on a spreadsheet that calculates payments based on various scenarios. I am trying to modify a formula from another spreadsheet that had more conditions. When I removed the extra conditions to simplify the formula for this spreadsheet, I keep getting a false and I can't figure out where my statement is wrong.

The formula consists of a series of nested if statements.

Please see attached. The formula I am concerned with is in cell D2 (highlighted in orange).

TIA!

**ChemistB** · 05-07-2010, 01:53 PM

Hard to determine exactly what you are looking for. I simplified your equation a bit by replacing all the nested IF statements regarding cell F2 with a CHOOSE Function. In any case, you don't have a statement for if B11/C#-B2>5000 is False. I showed where this would be in the formula with a "?". Also, are you sure you dont' want that to be B11/Sheet2!C#-B2 < 5000, "<5000" ?

=IF(B2=0,"",IF(CHOOSE(MATCH(F2,{24,36,48,60},0),B11/Sheet2!C6,B11/Sheet2!C7,B11/Sheet2!C8,B11/Sheet2!C9)-B2>5000,"<$5000","?"))

Hope that helped.

**msbing916** · 05-07-2010, 02:08 PM

I am not familiar with the choose function but it does look a lot easier. However, I am trying to have the formula calculate if the result is greater than 5000, based on the term (24,36, etc), if not show <$5000 rather than the "?". Does that explain it better?

**sailepaty** · 05-07-2010, 03:34 PM

Maybe this,

=IF(B2,IF(B11/VLOOKUP(F2,Sheet2!$A$6:$D$9,3,TRUE)-B2>5000,B11/VLOOKUP(F2,Sheet2!$A$6:$D$9,3,TRUE)-B2,"<$5000"),"")

Regards

**royUK** · 05-07-2010, 03:38 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Edited this time