Need help with excel formula

[hussain820]

Hi excel experts,

I need help with a formula, I have employee insurance sheet and I need per employee cost to company to be calculated (write now I am doing it by using =sum formula), the conditions are if an employee is single then the insurnace cost will be as it is in the CostPerEmployee column with blue text, and if an employee has spouse then it should be employee insurnace cost + spouse insurance cost in CostPerEmployee column with red text and finally if employee has spouse and children then it should be employee+spouse+children in the Cost PerEmployee column with red text.
I am attaching a sample data,
Any help is highly appreciated.

thanks

[#2]

@hussain820 

I'm responding as a person who before retiring was the director of the HR and Payroll database of a major US corporation. You may be in a small company with only a few employees, but if you have even a hundred, certainly if you're in the thousands, you will need to be designing your database in a different manner than you have displayed it here.

I hope you are open to having your data laid out in a wholly different way, specifically:

arrayed as a table,

• one row per employee
• marital status
• Spouse (Y/N)
• Child (#)

When you do that, the formula is relatively simple, one formula per employee, copied down to all the other rows. This is a simple example, and is here as an image, but I've also attached the file from which this was taken.

[#3]

@mathetes 

Hi and thank you very much for your help and advice, and what can I say the HR is keen on this kind of data presentation (with name, type and amount, subtotal), I have seen the formulae and it surely helps to calculate the cost per employee the only issue is that if the cost of insurance changes as per age and medical condition the formula needs to be changed as well, is there any way to make it dynamic.

Thanks

[#4]

@hussain820 

Done for the rate info I have but not as per age and medical condition since you supplied no conditions and their effects.

But yes, a table that is used as the basis for the rates is very possible and the desirable way to do it, so you can change any given rate just once and have all the formulas automatically adjust because they're really using the table. 

If you want to get more specific, you'd have to show us how age or medical conditions change things so we could incorporate those into a table. 

Are you IN HR? Or are you IT support to HR? Your role in all this is unclear.