I am attempting to calculate premium rates for age banded life insurance. The problem is that there are 9 age bands, and the IF/THEN function caps out at 7 tests. Can anyone help me translate the formula below into a LOOKUP formula so that I can include all 9 tests?
IF((P3<30, R3*.07, IF(AND(P3<35, >=30, R3*.09)), IF(AND(P3<40, >=35, R3*.11)), IF(AND(P3<45, >=40, R3*.15)), IF(AND(P3<50, >=45, R3*.24)), IF(AND(P3<55, >=50, R3*.36)), IF(AND(P3<60, >=55, R3*.62)), IF(AND(P3<65, >=60, R3*.87)), P3>=65, R3*1.51))
Thank you!
Bookmarks