IF statement with logical_value that is similar to this 0<a1<10

[Mortgage Man]

I am wanting a formula to calculate mortgage insurance.
If the equity is greater than or equal to 20% the answer should be 0. If
the equity is less than 20% but greater than 15% the answer should be a
formula that looks like this (equity*0.0036/12). This continues up to 0%
equity and the formula below is what I have so far. It gives me the right
answer when the equity is greater than or equal to 20% but just says "false"
when the equity is between 15% and 20%. I am guessing my problem is with
this part of the formula (0.15<=B6<0.2)

=IF(B6>=0.2,"0",IF(0.15<=B6<0.2,B7*0.0036/12))

 

[pdberger]

Mortgage Man --

I think your 2nd 'if' statement is missing an option. As you know the 'if'
statement has three parts: the criterium, what to do if it's true, and what
to do if it's false. Your first 'if' statement has the three parts (the 2nd
'if' statement is the false part of the first). But the 2nd 'if' statement
doesn't appear to me to have the 3rd component.

I think if you add that you should be fine?

hth

 

[Duke Carey]

Your statement of the issue is contradictory. If the calculation is
(equity(do you mean debt?)*0.0036/12) when the equity is less than 20%, you'd
simply use

=B7*0.0036/12*B6<.2

However, you also mention 15% as some kind of threshold. Is there a
different calculation for equity less than 15%. If so, then....

=IF(B6>=0.2,"0",IF(B6>=.15,B7*0.0036/12,what's the formula for 0% to 14.9%))

 

[JE McGimpsey]

One way:

=(B6<0.2)*(0.0036*(B6>=0.15))*B6/12

what happens when equity is <15%?

To do it with IF() statements:

=IF(B6>=0.2,0,IF(B6>=0.15,0.0036,"less than 15% equity))*B6/12

 

[Harlan Grove]

Duke Carey wrote...

Your statement of the issue is contradictory. If the calculation is
(equity(do you mean debt?)*0.0036/12) when the equity is less than 20%, you'd
simply use

=B7*0.0036/12*B6<.2

Equity usually means the borrower's principal balance, i.e., the sum of
the down payment and principal payments to date.

Your formula above will return TRUE or FALSE, never a numeric result,
because Excel *ALWAYS* gives higher precedence to arithmetic operators
than to comparison operators. In other words, this evaluates the same
as

=(B7*0.0036/12*B6)<.2

while it would appear the OP needs

=B7*0.0036/12*(B6<.2)

Simple Rule: *ALWAYS* parenthesize boolean expressions used as operands
to arithmetic operators.

However, you also mention 15% as some kind of threshold. Is there a
different calculation for equity less than 15%. If so, then....

=IF(B6>=0.2,"0",IF(B6>=.15,B7*0.0036/12,what's the formula for 0% to 14.9%))

....

If the result should be zero outside the 15% to 20% window, another
alternative would be

=(ABS(B6-0.175)<0.025)*B7*0.0036/12

 

[Duke Carey]

Equity usually means the borrower's principal balance, i.e., the sum of

the down payment and principal payments to date.

You're right, Harlan. However, the formula would converge to zero as the
borrower had less & less equity, which is (I'm guessing) the opposite of the
intended result

=B7*0.0036/12*B6<.2

That's what happens when you slap an answer down without testing 'cause you
have to step out for a meeting.....