Calculate total cost with multiple criteria

[md7899]

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.

 

[AlphaFrog]

It is very likely that your nested IF formula could be shortened. Can't give you specifics without seeing your current formula. A short example data set would also be very helpful.

 

[md7899]

Here's a sample:

Currently the bonus calc & total cost works only if a group B person is here for 12 months.

Excel Workbook
	A	B	C	D	E	F	G	H	I
2				31-Dec-11
3
4	Name	Group	Begin Date	End Date	Eligibility	Base Amt	Bonus	Time (in months)	Total Cost
5	Person 2	A	1-Jan-11	15-Aug-11	Y	25,000.00	$750.00	7.5	16,093.75
6	Person 3	A	1-Sep-11		N	31,000.00	$0.00	4.0	10,333.33
7	Person 4	A	1-Jan-11		Y	20,000.00	$600.00	12	20,771.67
8	Person 5	A	7-Mar-11		N	36,750.00	$0.00	10	30,625.00
9	Person 11	A	1-Jan-11		Y	33,000.00	$990.00	12	34,273.25
10	Person 12	A	1-Jan-11	16-Sep-11	Y	33,500.00	$1,005.00	8.6	24,728.58
11	Person 1	B	1-Jan-11		Y	35,000.00	$87.50	12	35,379.90
12	Person 6	B	1-Jan-11	30-Jun	N	40,000.00	$0.00	6.0	20,000.00
13	Person 7	B	1-Jan-11		Y	41,000.00	$102.50	12	41,445.02
14	Person 8	B	12-Sep-11		N	40,000.00	$0.00	3.7	12,333.33
15	Person 9	B	1-Jan-11		Y	42,000.00	$105.00	12	42,455.88
16	Person 10	B	1-Jan-11		Y	43,800.00	$109.50	12	44,275.41
17	Person 13	B	1-Jan-11		Y	16,690.44	$41.73	12	16,871.60
Sheet2

Excel 2003

Cell Formulas
Range	Formula
G5	=IF(E5="Y",IF(B5="A",F5*0.03,((F5*0.01)/12)*3),0)
G6	=IF(E6="Y",IF(B6="A",F6*0.03,((F6*0.01)/12)*3),0)
G7	=IF(E7="Y",IF(B7="A",F7*0.03,((F7*0.01)/12)*3),0)
G8	=IF(E8="Y",IF(B8="A",F8*0.03,((F8*0.01)/12)*3),0)
G9	=IF(E9="Y",IF(B9="A",F9*0.03,((F9*0.01)/12)*3),0)
G10	=IF(E10="Y",IF(B10="A",F10*0.03,((F10*0.01)/12)*3),0)
G11	=IF(E11="Y",IF(B11="A",F11*0.03,((F11*0.01)/12)*3),0)
G12	=IF(E12="Y",IF(B12="A",F12*0.03,((F12*0.01)/12)*3),0)
G13	=IF(E13="Y",IF(B13="A",F13*0.03,((F13*0.01)/12)*3),0)
G14	=IF(E14="Y",IF(B14="A",F14*0.03,((F14*0.01)/12)*3),0)
G15	=IF(E15="Y",IF(B15="A",F15*0.03,((F15*0.01)/12)*3),0)
G16	=IF(E16="Y",IF(B16="A",F16*0.03,((F16*0.01)/12)*3),0)
G17	=IF(E17="Y",IF(B17="A",F17*0.03,((F17*0.01)/12)*3),0)
H5	=ROUND(IF(D5>0,(D5-C5)/30,($D$2-C5)/30),1)
H6	=ROUND(IF(D6>0,(D6-C6)/30,($D$2-C6)/30),1)
H7	=ROUND(IF(D7>0,(D7-C7)/30,($D$2-C7)/30),1)
H8	=ROUND(IF(D8>0,(D8-C8)/30,($D$2-C8)/30),1)
H9	=ROUND(IF(D9>0,(D9-C9)/30,($D$2-C9)/30),1)
H10	=ROUND(IF(D10>0,(D10-C10)/30,($D$2-C10)/30),1)
H11	=ROUND(IF(D11>0,(D11-C11)/30,($D$2-C11)/30),1)
H12	=ROUND(IF(D12>0,(D12-C12)/30,($D$2-C12)/30),1)
H13	=ROUND(IF(D13>0,(D13-C13)/30,($D$2-C13)/30),1)
H14	=ROUND(IF(D14>0,(D14-C14)/30,($D$2-C14)/30),1)
H15	=ROUND(IF(D15>0,(D15-C15)/30,($D$2-C15)/30),1)
H16	=ROUND(IF(D16>0,(D16-C16)/30,($D$2-C16)/30),1)
H17	=ROUND(IF(D17>0,(D17-C17)/30,($D$2-C17)/30),1)
I5	=IF(F5>0,(F5+G5)/12*H5,0)
I6	=IF(F6>0,(F6+G6)/12*H6,0)
I7	=IF(F7>0,(F7+G7)/12*H7,0)
I8	=IF(F8>0,(F8+G8)/12*H8,0)
I9	=IF(F9>0,(F9+G9)/12*H9,0)
I10	=IF(F10>0,(F10+G10)/12*H10,0)
I11	=IF(F11>0,(F11+G11)/12*H11,0)
I12	=IF(F12>0,(F12+G12)/12*H12,0)
I13	=IF(F13>0,(F13+G13)/12*H13,0)
I14	=IF(F14>0,(F14+G14)/12*H14,0)
I15	=IF(F15>0,(F15+G15)/12*H15,0)
I16	=IF(F16>0,(F16+G16)/12*H16,0)
I17	=IF(F17>0,(F17+G17)/12*H17,0)

 

[md7899]

Hi again,

Anyone able to help. Thanks.