Calculate total cost with multiple criteria

md7899

New Member
Joined
Oct 2, 2011
Messages
9
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
Here's a sample:

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

Excel Workbook
ABCDEFGHI
231-Dec-11
3
4NameGroupBegin DateEnd DateEligibilityBase AmtBonusTime (in months)Total Cost
5Person 2A1-Jan-1115-Aug-11Y25,000.00$750.007.516,093.75
6Person 3A1-Sep-11N31,000.00$0.004.010,333.33
7Person 4A1-Jan-11Y20,000.00$600.001220,771.67
8Person 5A7-Mar-11N36,750.00$0.001030,625.00
9Person 11A1-Jan-11Y33,000.00$990.001234,273.25
10Person 12A1-Jan-1116-Sep-11Y33,500.00$1,005.008.624,728.58
11Person 1B1-Jan-11Y35,000.00$87.501235,379.90
12Person 6B1-Jan-1130-JunN40,000.00$0.006.020,000.00
13Person 7B1-Jan-11Y41,000.00$102.501241,445.02
14Person 8B12-Sep-11N40,000.00$0.003.712,333.33
15Person 9B1-Jan-11Y42,000.00$105.001242,455.88
16Person 10B1-Jan-11Y43,800.00$109.501244,275.41
17Person 13B1-Jan-11Y16,690.44$41.731216,871.60
Sheet2
Excel 2003
Cell Formulas
RangeFormula
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)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top