If when and formula

Hi All,

I previously submitted a post with a very specific request for formula. I decided to break the procedure in half, as all of my rules will not fit in one calculation cell.

What I have so far is attached.

Now, I am looking for a formula that will round to a specific number if two other cells meet the conditions. My parameters are below.

E9 is to = $1000000, ONLY IF B9= 75%, AND IF C9 is less than $1000000
E9 is to = $1500000, ONLY IF B9= 70%, AND IF C9 is less than $1500000
E9 is to = $2000000, ONLY IF B9= 65%, AND IF C9 is less than $2000000

Both B9 and C9 are formulas, not sure if that makes a difference?

Please help!

Originally Posted by Sambo kid

based on what you wrote AND what you posted, this will work...
=IF(AND(B9="75%",C9<10000000),1000000,IF(AND(B9="70%",C9<1500000),1500000,IF(AND(B9="65%",C9<2000000),2000000,"")))
or essentially the same but w/o the blank...
=IF(AND(B9="75%",C9<10000000),1000000,IF(AND(B9="70%",C9<1500000),1500000,IF(AND(B9="65%",C9<2000000),2000000)))

Sambo, it does work, however, its rounding even when its not supposed to....

e.g. when after improved is 1400000, it multiples 75% like it should, which gives the loan amount of 1050000. However the E9 cell that is to round only when multiplied by 75 AND the result is LESS THAN 1000000, so it should be staying as 1050000 because it is not LESS THAN 1000000...

Originally Posted by TMS

I like that approach but you don't really need the IFs.

Formula:

Please Login or Register  to view this content.

Regards, TMS

When using this formula, it gives the same result as it did using Sambos, except when loan amount in 1050000, E9 becomes "0"

I know this is all types of confusing and complicated, but I started over, which is why I used this post and not the old question I posted.

I ONLY NEED THE ROUNDING/NOT ROUNDING FORMULA.



1. ONLY WHEN B9= 75% AND C9 is less than $1000000, E9 is to round up to $1000000 (answers between 1000000 to 1500000 are to be left as is and NOT rounded)

2. ONLY WHEN B9= 70% AND C9 is less than $1500000, E9 is to round up to $1500000 (answers between 1500000 to 1999970 are to be left as is and NOT rounded)

3. ONLY WHEN B9= 65% AND C9 is less than $2000000, E9 is to round up to $2000000 (answers between 2000000 and 2999945 are to be left as is and NOT rounded)
|________________| |___________________| |_______________________|
TRUE TRUE FOR THIS TO APPLY


Each rule above only applies to that specific percentage. So, if the After improved value is 2857100, which multiplies at 70% per the rule, the maximum loan would be 1999970. It would NOT round to 2MM because the amount is not less than 1.5 and the rounding to 2MM rule only applies when using 65%, so the answer would stay 1999970.

For instance:
After Improved Value:$2,500,000
Maximum Loan @70%: $1,750,000
At 70%, it is to remain $1,750,000 because it is not less than 1500000





The page I am going off of is explained like this:

If After improved value is less than or equal to 1250000 then multipy it by 80%.
If the after improved cost is greater than 1250000 but less than or equal to 2000000 multiply it by 75%. When it is multiplied by 75% and the result (max loan amount) is less than 1000000, round up to 1000000. If it is over, let it be.
If the after improved cost is greater than 2000000 but less than or equal to 2857100 multiply it by 70%. When it is multiplied by 70% and the result is less than 1500000, round up to 1500000. If it is over, let it be.
If the after improved cost is greater than 2857100 but less than or equal to 4615300 multiply it by 65%. When it is multiplied by 65% and the result is less than 2000000, round up to 2000000. If it is over, let it be.