Forum Discussion
MS Excel 2013 Formula Assistance
Aloha Guys, badly needed your assistance on this scenario attached. Response is very much appreciated. Thanks.
Problem: I need the answer/formula on how to arrive to the total due insurance.
2 Replies
Apparently you aren't as desperate as you sounded, since you've not responded to my questions. So the solution in the attached is only tentative, since I had to work with what you provided in the first instance.
In the attached, I've created four cells into which you can enter Age, Amount, No of Days, and Term. I've also applied those as names to the corresponding cells, so the formulas that follow refer to those range names rather than cell references.
I then have one formula that determines the "Rate" based on the table you provided.
That formula is the longest one, and here it is.
=IFS(Age<18,0,
AND(Age<=65,Amount<=500000),1,
AND(Age<=65,Amount>500000),0.6,
AND(Age<=70,Amount<=350000),3.5,
AND(Age<=70,Amount>350000),4.5,
AND(Age<=75,Amount<=350000),4.5,
AND(Age<=75,Amount>350000),5.5)
The final formula follows your direction and takes all of the info (ignoring "No of Days" because that seems to be irrelevant) as follows:
=(Amount/1000)*Rate*Term
If it's not what you expect, please come back with further clarification.
Some points of clarification, if I might.
This looks more like you should be asking for the total premium due, rather than the total insurance. Is that right? Just trying to understand the context.
Could you clear up what the term "Term" refers to. You display a range from 1 to 60, but then say it should be a maximum of 12. What is it? And does it refer to months? Or something else? How does it relate to "# of Days"? Are the two redundant? If not, what purpose does #ofDays serve on this sheet?
And below 18 or above 75 in age means no coverage; right?
