Adjust Calculation based on 2x conditions in multiple cells?

Vexorg

Board Regular
Joined
Oct 5, 2010
Messages
116
Hello!

This pertains to a model I am using to calculate interest rates for mortgages. Rates can be floating or fixed and the interest rate calculation method can either be Actual/360 or 30/360.

There are 2x cells with dropdown choice similar to the below. (E3 & E4) which have values, and depending on the combination of these values (e.g. 3&1 or 2&2) I want to adjust the formula that is used to determine the output.

Column E
Current Choice (Drop-down data validation)Choices
Row 3Rate TypeFixedFixed (value=3), Floating (value=2)
Row 4Interest CalculationActual/360Actual/360 (value=1), 30/360 (value=2)

<tbody>
</tbody>


As of right now, the interest rate is calculated in cell F37. The calculation is being determined by the value in cell E3, however I would like to adjust the calculation based on the value in E4.

If E4 = "Actual/360" (or "1") the interest rate in F37 should be divided by 360 and then multiplied by 365.

Is the correct way to resolve this to use an =IF(AND( statement to adjust the calculation based on the criteria in cell E3 & E4? I am assuming I would have to set up all of my combinations separately unless there is an easier way to do this?

Regards,

Vexorg
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Vexorg

Board Regular
Joined
Oct 5, 2010
Messages
116
I was able to figure it out with a nested if:

=IF(AND($E$3=2,$E$4=1),('Test 1'!I37+'Test 1'!$E$5/10000)/360*365,IF(AND($E$3=2,$E$4=2),'Test 1'!I37+'Test 1'!$E$5/10000,IF(AND($E$3=3,$E$4=1),(('Test 1'!$E$6+'Test 1'!$E$5)/10000)/360*365,IF(AND($E$3=3,$E$4=2),('Test 1'!$E$6+'Test 1'!$E$5)/10000))))
 

Watch MrExcel Video

Forum statistics

Threads
1,095,348
Messages
5,443,936
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top