Hello,
I'm wondering if this is possible in Excel 2003. In one spreadsheet I'd like to determine the total cost for each person in a group given different criteria. There are 2 groups - A & B. Group A has a bonus date of Jan 1 so if person #1 has a begin date of Jan 1 then they get the bonus say 3%. Person #2 has begin date of Mar 4 then they don't get the bonus this year but I still need to calculate the total cost for #2 . Each person can also have an end at any point in time during the year or they may have no end date and again the annual cost is needed. I have formulas worked out for all the different options for Group A.
There's now a second group - group B with a bonus date of Sept 1 and if you are here at this bonus date you get the bonus - e.g. for those who have a begin date of Jan 1 and who are still here on Sept 1 they will get a bonus. If you have a begin date of Nov 10 then no bonus or begin date in March and end date in July then no bonus.
I would like to combine the data for Group A and B onto one spreadsheet and have formulas set up to automatically calculate the total cost (which would include the bonus). However because the bonus calculation differs depending on the group each person is in and I also have to account for those who don't get a bonus or who may have an end date I'm having too many nested if statements.
Any ideas?
Thanks in advance.
I'm wondering if this is possible in Excel 2003. In one spreadsheet I'd like to determine the total cost for each person in a group given different criteria. There are 2 groups - A & B. Group A has a bonus date of Jan 1 so if person #1 has a begin date of Jan 1 then they get the bonus say 3%. Person #2 has begin date of Mar 4 then they don't get the bonus this year but I still need to calculate the total cost for #2 . Each person can also have an end at any point in time during the year or they may have no end date and again the annual cost is needed. I have formulas worked out for all the different options for Group A.
There's now a second group - group B with a bonus date of Sept 1 and if you are here at this bonus date you get the bonus - e.g. for those who have a begin date of Jan 1 and who are still here on Sept 1 they will get a bonus. If you have a begin date of Nov 10 then no bonus or begin date in March and end date in July then no bonus.
I would like to combine the data for Group A and B onto one spreadsheet and have formulas set up to automatically calculate the total cost (which would include the bonus). However because the bonus calculation differs depending on the group each person is in and I also have to account for those who don't get a bonus or who may have an end date I'm having too many nested if statements.
Any ideas?
Thanks in advance.