I have a spreadsheet that I am modifying and need it to autopopulate the premium and eligible discount columns based on the previous columns of Group ID, Class ID, Sold on or before, Tobacco Use, Gender, Premium Year and Age Band.
The actual spreadsheet will have multiple Group and Class IDs, the one attached is duplicated because it has all the different factors listed in each row. So it's possible the premium and discount answer is based on a different row for each of the criteria.
What is the best way to go about this? Dependent Drop Downs? VLookup? Would a dashboard be helpful? I'm by no means an expert and have been looking at different videos on what I might need but I think I'm just confusing myself. Can someone please direct me to the functions I need to do this?
Hi Naomi,
Can you please explain the logic that decides how the Premium and Eligible Discount columns are populated.
Regards
Phil
The Group ID and Class ID identify the name of an insurance plan. That is all that is needed to determine if it is eligible for a discount.
However, premiums also depend on when it was purchased (before or after June 1, 2010), gender (unisex, male female), tobacco use (smoker, non-smoker) and the current year (2021, 2022).
The attachment is not a complete spreadsheet off all Group IDs and Class IDs.
Hi Naomi,
OK but I don't know how to implement your description into a solution in Excel. You say that Group ID and Class ID identify the name of an insurance plan. That is all that is needed to determine if it is eligible for a discount. But how? How do I know what values for Group ID and Class ID result in a discount?
Same for Premiums. What values of those other columns results in a premium?
regards
Phil
I've provided an attachment with what I'm working with. There are columns with the premium and if the plan is eligible for a discount, also the amount of the discount. On the third sheet, I've put in the information I would like to get from sheet 1. My formulas aren't working. I'm just not good and formulas within formulas, not enough experience.
As you can see, there are multiple rows with the same Group ID and Class ID but the premium depends on the additional criteria such as age and possible tobacco use and gender.
