I am struggling on some formula issues in Excel, I know that I have to use the V
ID: 2771140 • Letter: I
Question
I am struggling on some formula issues in Excel, I know that I have to use the VLOOKUP for a couple of them, but I dont know how to actually multiply them by the D column once the VLOOKUP formula is sorted in order to actually come to the values. I cannot simply just apply a single formula to the single cell as the formula will have to work for the entire column as specified.
The second image is the Benefits Code
A. Insert a function in cell E5 to display the pension cost for the first employee, based on the Benefits Code.
B. Insert a function in cell F5 to display the insurance cost for the first employee, based on the Benefits Code.
C. Insert a function in cell G5 to display the daycare cost for the first employee, based on the Benefits Code..
D. Insert a function in cell H5 to calculate Total Employee Cost for the first customer. If the customer needs daycare, the total cost should include that column amount. If the customer does not need daycare the total cost should not include that column amount.
E. Copy the four formulas down their respective columns.
BENEFITS CODE
Fireball Technoioay Company 2 Salary nd Benefi Expend cures 4 Employee Benefits Code Deycare Salary Pension Costs Health Insurance Costs Daycare Costs Employee Cost 8 Summary Statistics 9 Number of Impiovees: 0 Lowest Total Emplayee Cost Averare Total Irployee Cast 52 Maxiram Tata. Employee 53 Median Total Emplayee C 54 Today's Date:Explanation / Answer
Fireball Technology Company Salary and Benefit Expenditures Employee Benefits Need Salary Pension Health Daycare Total Code Daycare Costs Insurance Costs Employee Costs Costs 1 2 No 39100 =VLOOKUP(B8,$A$55:$D$57,2,FALSE)*D8 =VLOOKUP(B8,$A$55:$D$57,3,FALSE)*D8 =IF(C8="No",0,VLOOKUP(B8,$A$55:$D$57,4,FALSE)) =SUM(D8:G8) =A8+1 1 Yes 37150 =VLOOKUP(B9,$A$55:$D$57,2,FALSE)*D9 =VLOOKUP(B9,$A$55:$D$57,3,FALSE)*D9 =IF(C9="No",0,VLOOKUP(B9,$A$55:$D$57,4,FALSE)) =SUM(D9:G9) =A9+1 3 Yes 32500 =VLOOKUP(B10,$A$55:$D$57,2,FALSE)*D10 =VLOOKUP(B10,$A$55:$D$57,3,FALSE)*D10 =IF(C10="No",0,VLOOKUP(B10,$A$55:$D$57,4,FALSE)) =SUM(D10:G10) =A10+1 3 Yes 34000 =VLOOKUP(B11,$A$55:$D$57,2,FALSE)*D11 =VLOOKUP(B11,$A$55:$D$57,3,FALSE)*D11 =IF(C11="No",0,VLOOKUP(B11,$A$55:$D$57,4,FALSE)) =SUM(D11:G11) =A11+1 1 No 35650 =VLOOKUP(B12,$A$55:$D$57,2,FALSE)*D12 =VLOOKUP(B12,$A$55:$D$57,3,FALSE)*D12 =IF(C12="No",0,VLOOKUP(B12,$A$55:$D$57,4,FALSE)) =SUM(D12:G12) =A12+1 2 No 42500 =VLOOKUP(B13,$A$55:$D$57,2,FALSE)*D13 =VLOOKUP(B13,$A$55:$D$57,3,FALSE)*D13 =IF(C13="No",0,VLOOKUP(B13,$A$55:$D$57,4,FALSE)) =SUM(D13:G13) =A13+1 3 Yes 29900 =VLOOKUP(B14,$A$55:$D$57,2,FALSE)*D14 =VLOOKUP(B14,$A$55:$D$57,3,FALSE)*D14 =IF(C14="No",0,VLOOKUP(B14,$A$55:$D$57,4,FALSE)) =SUM(D14:G14) =A14+1 3 No 34000 =VLOOKUP(B15,$A$55:$D$57,2,FALSE)*D15 =VLOOKUP(B15,$A$55:$D$57,3,FALSE)*D15 =IF(C15="No",0,VLOOKUP(B15,$A$55:$D$57,4,FALSE)) =SUM(D15:G15) =A15+1 3 No 30450 =VLOOKUP(B16,$A$55:$D$57,2,FALSE)*D16 =VLOOKUP(B16,$A$55:$D$57,3,FALSE)*D16 =IF(C16="No",0,VLOOKUP(B16,$A$55:$D$57,4,FALSE)) =SUM(D16:G16) =A16+1 1 No 35200 =VLOOKUP(B17,$A$55:$D$57,2,FALSE)*D17 =VLOOKUP(B17,$A$55:$D$57,3,FALSE)*D17 =IF(C17="No",0,VLOOKUP(B17,$A$55:$D$57,4,FALSE)) =SUM(D17:G17) =A17+1 3 Yes 33000 =VLOOKUP(B18,$A$55:$D$57,2,FALSE)*D18 =VLOOKUP(B18,$A$55:$D$57,3,FALSE)*D18 =IF(C18="No",0,VLOOKUP(B18,$A$55:$D$57,4,FALSE)) =SUM(D18:G18) =A18+1 3 Yes 28600 =VLOOKUP(B19,$A$55:$D$57,2,FALSE)*D19 =VLOOKUP(B19,$A$55:$D$57,3,FALSE)*D19 =IF(C19="No",0,VLOOKUP(B19,$A$55:$D$57,4,FALSE)) =SUM(D19:G19) =A19+1 2 Yes 36000 =VLOOKUP(B20,$A$55:$D$57,2,FALSE)*D20 =VLOOKUP(B20,$A$55:$D$57,3,FALSE)*D20 =IF(C20="No",0,VLOOKUP(B20,$A$55:$D$57,4,FALSE)) =SUM(D20:G20) =A20+1 2 No 37300 =VLOOKUP(B21,$A$55:$D$57,2,FALSE)*D21 =VLOOKUP(B21,$A$55:$D$57,3,FALSE)*D21 =IF(C21="No",0,VLOOKUP(B21,$A$55:$D$57,4,FALSE)) =SUM(D21:G21) =A21+1 3 No 34000 =VLOOKUP(B22,$A$55:$D$57,2,FALSE)*D22 =VLOOKUP(B22,$A$55:$D$57,3,FALSE)*D22 =IF(C22="No",0,VLOOKUP(B22,$A$55:$D$57,4,FALSE)) =SUM(D22:G22) =A22+1 1 Yes 36500 =VLOOKUP(B23,$A$55:$D$57,2,FALSE)*D23 =VLOOKUP(B23,$A$55:$D$57,3,FALSE)*D23 =IF(C23="No",0,VLOOKUP(B23,$A$55:$D$57,4,FALSE)) =SUM(D23:G23) =A23+1 3 Yes 33000 =VLOOKUP(B24,$A$55:$D$57,2,FALSE)*D24 =VLOOKUP(B24,$A$55:$D$57,3,FALSE)*D24 =IF(C24="No",0,VLOOKUP(B24,$A$55:$D$57,4,FALSE)) =SUM(D24:G24) =A24+1 3 Yes 34000 =VLOOKUP(B25,$A$55:$D$57,2,FALSE)*D25 =VLOOKUP(B25,$A$55:$D$57,3,FALSE)*D25 =IF(C25="No",0,VLOOKUP(B25,$A$55:$D$57,4,FALSE)) =SUM(D25:G25) =A25+1 1 No 34500 =VLOOKUP(B26,$A$55:$D$57,2,FALSE)*D26 =VLOOKUP(B26,$A$55:$D$57,3,FALSE)*D26 =IF(C26="No",0,VLOOKUP(B26,$A$55:$D$57,4,FALSE)) =SUM(D26:G26) =A26+1 3 Yes 34000 =VLOOKUP(B27,$A$55:$D$57,2,FALSE)*D27 =VLOOKUP(B27,$A$55:$D$57,3,FALSE)*D27 =IF(C27="No",0,VLOOKUP(B27,$A$55:$D$57,4,FALSE)) =SUM(D27:G27) =A27+1 3 Yes 30000 =VLOOKUP(B28,$A$55:$D$57,2,FALSE)*D28 =VLOOKUP(B28,$A$55:$D$57,3,FALSE)*D28 =IF(C28="No",0,VLOOKUP(B28,$A$55:$D$57,4,FALSE)) =SUM(D28:G28) =A28+1 2 No 40000 =VLOOKUP(B29,$A$55:$D$57,2,FALSE)*D29 =VLOOKUP(B29,$A$55:$D$57,3,FALSE)*D29 =IF(C29="No",0,VLOOKUP(B29,$A$55:$D$57,4,FALSE)) =SUM(D29:G29) =A29+1 2 No 36000 =VLOOKUP(B30,$A$55:$D$57,2,FALSE)*D30 =VLOOKUP(B30,$A$55:$D$57,3,FALSE)*D30 =IF(C30="No",0,VLOOKUP(B30,$A$55:$D$57,4,FALSE)) =SUM(D30:G30) =A30+1 2 No 37000 =VLOOKUP(B31,$A$55:$D$57,2,FALSE)*D31 =VLOOKUP(B31,$A$55:$D$57,3,FALSE)*D31 =IF(C31="No",0,VLOOKUP(B31,$A$55:$D$57,4,FALSE)) =SUM(D31:G31) =A31+1 1 Yes 42850 =VLOOKUP(B32,$A$55:$D$57,2,FALSE)*D32 =VLOOKUP(B32,$A$55:$D$57,3,FALSE)*D32 =IF(C32="No",0,VLOOKUP(B32,$A$55:$D$57,4,FALSE)) =SUM(D32:G32) =A32+1 1 Yes 42000 =VLOOKUP(B33,$A$55:$D$57,2,FALSE)*D33 =VLOOKUP(B33,$A$55:$D$57,3,FALSE)*D33 =IF(C33="No",0,VLOOKUP(B33,$A$55:$D$57,4,FALSE)) =SUM(D33:G33) =A33+1 1 No 44000 =VLOOKUP(B34,$A$55:$D$57,2,FALSE)*D34 =VLOOKUP(B34,$A$55:$D$57,3,FALSE)*D34 =IF(C34="No",0,VLOOKUP(B34,$A$55:$D$57,4,FALSE)) =SUM(D34:G34) =A34+1 1 No 40550 =VLOOKUP(B35,$A$55:$D$57,2,FALSE)*D35 =VLOOKUP(B35,$A$55:$D$57,3,FALSE)*D35 =IF(C35="No",0,VLOOKUP(B35,$A$55:$D$57,4,FALSE)) =SUM(D35:G35) =A35+1 2 No 44000 =VLOOKUP(B36,$A$55:$D$57,2,FALSE)*D36 =VLOOKUP(B36,$A$55:$D$57,3,FALSE)*D36 =IF(C36="No",0,VLOOKUP(B36,$A$55:$D$57,4,FALSE)) =SUM(D36:G36) =A36+1 1 Yes 48500 =VLOOKUP(B37,$A$55:$D$57,2,FALSE)*D37 =VLOOKUP(B37,$A$55:$D$57,3,FALSE)*D37 =IF(C37="No",0,VLOOKUP(B37,$A$55:$D$57,4,FALSE)) =SUM(D37:G37) =A37+1 1 Yes 42000 =VLOOKUP(B38,$A$55:$D$57,2,FALSE)*D38 =VLOOKUP(B38,$A$55:$D$57,3,FALSE)*D38 =IF(C38="No",0,VLOOKUP(B38,$A$55:$D$57,4,FALSE)) =SUM(D38:G38) =A38+1 2 No 47500 =VLOOKUP(B39,$A$55:$D$57,2,FALSE)*D39 =VLOOKUP(B39,$A$55:$D$57,3,FALSE)*D39 =IF(C39="No",0,VLOOKUP(B39,$A$55:$D$57,4,FALSE)) =SUM(D39:G39) =A39+1 2 No 47000 =VLOOKUP(B40,$A$55:$D$57,2,FALSE)*D40 =VLOOKUP(B40,$A$55:$D$57,3,FALSE)*D40 =IF(C40="No",0,VLOOKUP(B40,$A$55:$D$57,4,FALSE)) =SUM(D40:G40) =A40+1 1 Yes 48500 =VLOOKUP(B41,$A$55:$D$57,2,FALSE)*D41 =VLOOKUP(B41,$A$55:$D$57,3,FALSE)*D41 =IF(C41="No",0,VLOOKUP(B41,$A$55:$D$57,4,FALSE)) =SUM(D41:G41) =A41+1 2 No 52500 =VLOOKUP(B42,$A$55:$D$57,2,FALSE)*D42 =VLOOKUP(B42,$A$55:$D$57,3,FALSE)*D42 =IF(C42="No",0,VLOOKUP(B42,$A$55:$D$57,4,FALSE)) =SUM(D42:G42) =A42+1 1 Yes 62520 =VLOOKUP(B43,$A$55:$D$57,2,FALSE)*D43 =VLOOKUP(B43,$A$55:$D$57,3,FALSE)*D43 =IF(C43="No",0,VLOOKUP(B43,$A$55:$D$57,4,FALSE)) =SUM(D43:G43) =A43+1 1 Yes 61250 =VLOOKUP(B44,$A$55:$D$57,2,FALSE)*D44 =VLOOKUP(B44,$A$55:$D$57,3,FALSE)*D44 =IF(C44="No",0,VLOOKUP(B44,$A$55:$D$57,4,FALSE)) =SUM(D44:G44) =A44+1 1 No 57000 =VLOOKUP(B45,$A$55:$D$57,2,FALSE)*D45 =VLOOKUP(B45,$A$55:$D$57,3,FALSE)*D45 =IF(C45="No",0,VLOOKUP(B45,$A$55:$D$57,4,FALSE)) =SUM(D45:G45) =A45+1 2 No 58500 =VLOOKUP(B46,$A$55:$D$57,2,FALSE)*D46 =VLOOKUP(B46,$A$55:$D$57,3,FALSE)*D46 =IF(C46="No",0,VLOOKUP(B46,$A$55:$D$57,4,FALSE)) =SUM(D46:G46) =A46+1 1 Yes 60050 =VLOOKUP(B47,$A$55:$D$57,2,FALSE)*D47 =VLOOKUP(B47,$A$55:$D$57,3,FALSE)*D47 =IF(C47="No",0,VLOOKUP(B47,$A$55:$D$57,4,FALSE)) =SUM(D47:G47) =A47+1 2 Yes 72000 =VLOOKUP(B48,$A$55:$D$57,2,FALSE)*D48 =VLOOKUP(B48,$A$55:$D$57,3,FALSE)*D48 =IF(C48="No",0,VLOOKUP(B48,$A$55:$D$57,4,FALSE)) =SUM(D48:G48) Benefits Code Code Pension Health Daycare Insurance Contribution 1 0.05 0.3 5000 2 0.075 0.25 4000 3 0.03 0.1 1000 This is the answer after showing all the formulae as applicable. Kindly copy the same in excel, annd remove the show formulas tab. Answers will immediately be reflected.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.