simple IF statement

[arkved]

I have a sheet that needs to check the value of a few different cells, then return a value depending on what is found. what i have so far is this:

Cell D3 = Current Value of home
Cell E3 = Current 1st mtg Balance
Cell F3 = Current 2nd mtg Balance
Cell G3 = Over Loan to Value (D3-E3-F3)
Cell H3 = At Risk (how much of F3 is at risk)

What i need is the Cell H3 to display one of 3 things, IF D3 is greater than the sum of E3 and F3, then H3 should display 0. If D3 is LESS than the sum of E3 and F3, then H3 should show the difference(which is what G3 shows). I can accomplish all of that with:

=IF(G3<=F3,SUM(F3,G3),0)

The problem i have is if D3 is, for example 100,000. E3 is 200,000. and F3 is 50,000, H3 will display -150,000. What i want it to display is a value no greater than F3.

To simplify, i want H3 to never be larger than F3, but never less than 0. I tried using multipe IF statements but i kept getting syntax errors and im not sure where i am going wrong.

Any help would be MUCH appreciated!

Thank you,

Joe

[teylyn]

If I understand you correctly, you only want to show SUM(F3,G3) as the result of the IF statement if it is less than F5 and F5 is 50000. If so, try

=IF(G3<=F3,min(F3,abs(SUM(F3,G3))),0)

[arkved]

Thanks for the fast response. F3 can be any value, 50,000 was just an example. Basically F3 is a 2nd mortgage. The equation works except when the value (D3) and the 1st morgage (E3) are lower than F3. for example, If i plug in 10,000 in D3, 35000 in E3, and 65,000 in F3. the result in H3 shows to be 25,000. The result i want to see would be full amount in F3 since the property is valued at 10,000, the first mortgage is 35,000, and the 2nd mortgage is 65,000. the full 65,000 is "at risk"

Again thank you for the prompt response, i hope i have cleared up any questions.

Joe

[boohah]

Hi Joe

Just wondering if the values for the 1st and 2nd mortgage are linked to each other or are separate values. Because by my logic if the total of both mortgages are 65,000 and the current value of the home is 10,000, the risk would be 55,000. Basically I am wondering whether you are correctly working out the value for cell G3 and that might be the cause of your problem.

Doug

[arkved]

Hi Doug,

the two values are seperate. I am only concerned with the risk of the 2nd mortgage. In order to calculate the risk, i have to take Value - 1st mort - 2nd mort. That gives me a value, that if negative, is risk to the 2nd mortgage. 100 - 75 - 50 = -25. in this example, there is $25dollars at risk to the 2nd mortgage. i would want to display $25. but, if for example, we had 50 - 75 - 50 = -75, I have $50 of risk on the 2nd mortgage. I am not concerned with the extra $25 that is at risk to the 1st mortgage, so i want to only display $50.

Thanks again.

[boohah]

Hi Joe

Copy and paste this formula into cell H3 and tell me if it helps

=IF(ABS(G3)>F3,F3,ABS(G3))

Doug

[arkved]

Hi Doug,

That almost works, the problem i have with it is lets say the value of the home is 500,000, they have no first mortgage, and the 2nd is 50,000. H3 ends up showing $50,000 when it should be 0. It should be 0 because the home is worth 500,000, and they only owe 50,000, so none of the 50,000 is at risk. The equation works fine if the first and second mortgage is greater than the home value, but i need to cover 3 possibilities. the 1st possibility is being there is no risk to the 2nd mortgage, the 2nd possibility is that there is some risk to the 2nd, and the 3rd is that the entire 2nd mortgage is at risk.

::Edit:: I was thinking the equation would be something along the lines of =IF(G3>F3,(IF(G3>F3,F3,G3)),0) but some how i have to tell the first IF statement that if G3<F3 then to put F3's value. Basically if G3 less than 0, then H3 should show 0. If G3 is greater than 0 but less than F3, H3 should show G3's value. If G3 is greater than F3, then H3 should show F3's value. ::/Edit::

::Edit 2:: I almost have it. =IF(G3>F3,(IF(G3>F3,F3,G3)),(IF(G3<F3,G3,0))) works except if G3 goes below 0. If G3 becomes a negative number, H3 shows that negative number. How do i tell Excel to show a value of 0 if G3 is a negative number? ::/Edit2::

Sorry if this is confusing, its not the easiest thing to explain, but i do greatly appreciate all the help.

Thanks again,

Joe

[arkved]

I think i may have figured this one out. i believe the following will do everything i need.

=IF(G3>F3,(IF(G3>F3,F3,G3)),(IF(AND(G3<F3, G3>0),G3,0)))

i just needed the AND in there to give me 2 scenarios, that being G3 being LESS THAN F3, AND G3 being GREATER THAN 0, to show the value, else it shows 0. If anyone sees anything wrong with that equation, id appreciate a heads up.

Thank you all again,

Joe