Forum Discussion
Need help with excel formula
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.
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
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.