Nested IF Statements Will Not Calculate

[MKuehner]

Not even my husband who is, and I quote, “an award winning analyst” could figure this one out. Any help would be greatly appreciated!

I am trying to calculate interest rates based on certain criteria. My formula is:
=IF(V9="f",X9,IF(V9="P",IF('Current Rates'!$B$4+Y9<AA9,AA9,IF('Current Rates'!$B$4+Y9>Z9,Z9,'Current Rates'!$B$4+Y9)),IF(V9="L",IF('Current Rates'!$B$5+Y9<AA9,AA9,IF('Current Rates'!$B$5+Y9>Z9,Z9,'Current Rates'!$B$5+Y9)))))

Where,
V=Rate Type, inputted as text; “F” for fixed, “P” for prime and “L” for libor
Y=Margin, inputted as a percentage
Z=Ceiling, inputted as a percentage
AA=Floor, inputted as a percentage
AB=Interest Rate, calculated as a percentage
X=Fixed Rate, inputted as a percentage
‘Current Rate’ $B$4 = Prime Rate, inputted as a percentage
‘Current Rate’ $B$5 = Libor Rate, inputted as a percentage

In my data, 'Current Rates'!$B$5 = 0.3538%, V9 = L, Y19=1.4500% and all other columns are blank. The interest rate is calculating as 0.0000% instead of 1.8038%. If I put text in column Z19, the formula calculates properly. If I put text in column AA19, the formula calculates as text.

I’m not tied to the nested if statements. Any formula that will calculate the rate based on type taking the floor, ceiling and margin into consideration will work.

Thanks so much!

[NBVC]

Try:

Please Login or Register  to view this content.

This ensures it only looks at Y9 and Z9 if there is a number in them....

[MKuehner]

Thanks for the response. Getting closer! The forulma still does not calculate the floor correctly. For example, if the floor is 9%, AB should calculate as 9.0000% instead of 1.8038%.

[MKuehner]

Nevermind, got it! I added an ISNUMBER for AA. Thank you so much for your help!

=IF(V19="F",X19,IF(V19="P",IF('Current Rates'!$B$4+Y19<AA19,AA19,IF('Current Rates'!$B$4+Y19>Z19,Z19,'Current Rates'!$B$4+Y19)),IF(V19="L",IF(AND('Current Rates'!$B$5+Y19<AA19,ISNUMBER(AA19)),AA19,IF(AND('Current Rates'!$B$5+Y19>Z19,ISNUMBER(Z19)),Z19,'Current Rates'!$B$5+Y19)))))

[NBVC]

That was my fault.. I put the wrong reference in the Isnumber() function...