Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF statement with logical_value that is similar to this 0
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)) |
#2
|
|||
|
|||
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 "Mortgage Man" wrote: 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)) |
#3
|
|||
|
|||
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%)) "Mortgage Man" wrote: 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)) |
#4
|
|||
|
|||
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 In article , Mortgage Man <Mortgage wrote: 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)) |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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..... "Harlan Grove" wrote: 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 |
#7
|
|||
|
|||
Thank you to everyone who replied. I was able to correct my formula with
your help. Here is my final formula: =IF(B6=0.2,"0",IF(B6=0.15,B7*0.0036/12,IF(B6=0.1,B7*0.0056/12,IF(B6=0.05,B7*0.0078/12,IF(B6=0.03,B7*0.009/12,IF(B6=0,B7*0.011/12)))))) "Mortgage Man" wrote: 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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
IF Statement nightmare | Excel Discussion (Misc queries) | |||
How to calculate a sum as one outcome of an IF statement | Excel Worksheet Functions | |||
What statement to use? | Excel Worksheet Functions | |||
7+ nested if statement? | Excel Worksheet Functions |