• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

return value if 3 conditions met

B

bumstead

New Member
Help I am stumped! I have been trying to write a formula for the following.

I have a range ("Annual") that has 8 possible outcomes. I need to get to the correct item in Annual, depending on whether 3 conditions are met.

User enters 3 amounts (loan term, loan amount, LTV). The user entries are compared to 3 criteria. There are 8 possible outcomes, depending on whether:
- loan term is <= or > 15 years
- loan amount is <= or > $625,500
- LTV <= or > 95% (if loan amount is > 625,500), OR 90% (if loan amount is <= 625,500)

Formula result is returned in cell $G$13. I've uploaded a sample file.

I tried INDEX MATCH but it's not really a lookup table, and nested IFs are a nightmare.

Any help whatsoever would be utterly utterly appreciated.
 

Attachments

Welcome to the forums!

See attached; I've tried to create what you're asking for...but this is pretty preliminary. Is it doing what you want?
 

Attachments

Thank you so much, EiBi and Chihiro! It works great, except for one thing.

Cells $E$5 to $E$8 need to be <= or > .90, as follows:
E5 <=.90
E6 >.90
E7 <=.90
E8 >.90

When I changed it, the formula got messed up.
Can't tell you how much I appreciate this!!
 
Ugh, as it turns out there is an additional factor that was originally overlooked :mad:
For loan amounts > 625K, the LTV can either be <=78, >78 and <=90, or >90, not just greater than/less than 90.

I tried to update the formula in the attached as follows:
=IF(F4>15, IF(F10>0.95,">.95","<=.95"), IF(F7<=625500, IF(F10>0.9,">.90",”<=.90”), IF(F10>0.9,">.90", IF(AND(F10>0.78, F10<=0.9),"<=.90","<=.78") ) ) )

It's quite convoluted -- I can't figure out why it's not working. Is there a better way to do this??

Thanks in advance if you have time to look at this.
 

Attachments

This should do it.
=IF(G4>15,IF(G10>0.95,">.95","<=.95"),IF(G10>0.9,">.90",IF(AND(G7>625500,G10<=0.9,G10>0.78),">.78 and <=.90",IF(AND(G7>625500,G10<=0.78),"<=.78","<=.90"))))
 

Attachments

My original path -- with the SUMPRODUCT formula -- works well for matching values or binary options, but it becomes much more cumbersome with ranges like >78 and <=90.

Now that we've got new criteria, I don't think my solution is going to be the best fit.

The direction Chihiro's has gone will work better, I think.
 
You must log in or register to reply here.
Back
Top