Excel Formula based on value of numeric ranges

[cnbales]

Trying for the following:

If D4 is 760 or greater then G21*.0041/12
If D4 is 740 - 759 then G21*.0059/12
If D4 is 720 - 739 then G21*.0073/12
If D4 is 700 - 719 then G21*.00.87/12
If D4 is 680 - 699 then G21*.0108/12
If D4 is 679 or less then "FHA"

This is what I tried and am getting an error on:

=IF(D4<680,"FHA","")*IF(D4<700,G21*1.08/12,"")*IF(D4<720,G21*0.0087/12,"")*IF(D4<740,G21*0.0073/12,"")*IF(D4>760,G21*0.0059/12,"")*IF(D4>759,G21*0.0041/12)

Thank you,

Chris

[MarvinP]

Hi Chris,

Your problem looks like a Vlookup formula with True as the 4th argument to me. See the attached. Change D4 and G21 to get different answers.
VLookup True for Chris.xlsx

[Tony Valko]

Try this...

=IF(D4="","",IF(D4<680,"FHA",G21*IF(D4>759,0.0041,IF(D4>739,0.0059,IF(D4>719,0.0073,IF(D4>699,0.0087,0.0108))))/12))

[cnbales]

Try this...

=IF(D4="","",IF(D4<680,"FHA",G21*IF(D4>759,0.0041,IF(D4>739,0.0059,IF(D4>719,0.0073,IF(D4>699,0.0087,0.0108))))/12))

Tony,

That solution worked exactly as I was wanting. Thank you for your help.

[cnbales]

Hi Chris,

Your problem looks like a Vlookup formula with True as the 4th argument to me. See the attached. Change D4 and G21 to get different answers.
Attachment 498126

Marvin,

Thank you for your help. Your answer solved the problem also.

[Tony Valko]

You're welcome. Thanks for the feedback!