how would i be able to do this formula in excel , please show me atleast what to input. thanks
- Complete the "variable cost percentage" column by using a VLOOKUP function. (Hint: use "State Type" as the lookup value and the information in the "VariableCostPct" tab as the table_array).
SafetyCoverInsurance-Data-SetA Q . Search Sheet '+ Share Insert Page Layout Formulas Data Review View Home 2 AutoSum X Cut Calibri (Body) * 11 . A- A- So Wrap Text General Fill Copy Insert Format Sort & E Merge & Center $ % 2 60 $.0 Conditional Format Cell Delete B Clear Filter Paste Formatting as Table Styles Format A1 X fx |Region L M N 0 P Q R S T U V W G H A B D E Variable Cost Variable Contribution Cost Margin Country 1 Region State Salesperson Insurance Type Sales Date of Sale Invoice No State Type Percentage Midwest Tom Auto 883.03 DIV-XI OTOOO0-2TOZ-V ZT/T/T USA TX 3 Southeast IN Jennifer Auto 532.41 1/1/17 A-2017-000012 TN-Auto USA USA 4 New England T Crissy Auto 388.97 1/1/17 A-2017-000016 CT-Auto 5 New England MA Crissy auto 821.25 1/1/17 A-2017-000017 MA-Auto USA USA 6 Auto 470.51 1/1/17 A-2017-000028 GA-Auto Southeas GA John 7 West UT Kellie Auto 552.27 1/1/17 A-2017-000029 UT-Auto USA 8 West NV Kellie 430.8 1/1/17 A-2017-000033 NV-Auto USA Auto 943.11 1/1/17 A-2017-000036 WA-Auto USA 9 Wes WA Pete Auto USA 10 North Central VIC Mari auto 401.6 1/1/17 A-2017-000037 MO-Auto USA 11 Southeast Frank Auto 476.35 1/1/17 A-2017-000041 MS-Auto USA 12 North Central VI icole Auto 468.17 1/1/17 A-2017-000046 MI-Auto 13 New England ME Auto 113.5 1/1/17 A-2017-000050 ME-Auto USA Sara VIT Peter Auto 509.05 1/1/17 A-2017-000059 MT-Auto USA 14 West DE Fred auto 520.28 1/1/17 A-2017-000061 DE-Auto USA 15 Northeast USA 16 New England ME Sara Auto 693.63 1/1/17 A-2017-000065 ME-Auto 17 Southeast Jennifer 665.89 1/1/17 A-2017-000078 TN-Auto USA Auto 519.56 1/1/17 A-2017-000089 KS-Auto USA 18 Midwest Tom Auto USA 19 Northeast OH Mike Auto 960.22 1/1/17 A-2017-000096 OH-Auto 20 New England MA Crissy Auto 497.6 1/1/17 A-2017-000099 MA-Auto Dave auto 1070.19 1/1/17 A-2017-000107 NE-Auto 21 Midwest USA 22 Northeast VA Matt Auto 955.79 1/1/17 A-2017-000113 VA-Auto USA 23 North Central KY Maric Auto 553.44 1/1/17 A-2017-000114 KY-Auto 24 Northeast 610.22 1/1/17 A-2017-000115 MD-Auto USA Fred Auto 25 Southeast John auto 505.55 1/1/17 A-2017-000117 FL-Auto OK Tom 553.44 1/1/17 A-2017-000122 OK-Auto 26 Midwest auto USA 27 North Central IA icole Auto 795.76 1/1/17 A-2017-000124 IA-Auto USA 28 Midwest Tom Auto 567.7 1/1/17 A-2017-000128 TX-Auto USA 29 Northeast Matt Auto 790.34 1/1/17 A-2017-000139 DC-Auto USA 30 North Central MN Nicole auto 429.63 1/1/17 A-2017-000142 MN-Auto 31 New England MA Crissy auto 346.92 1/1/17 A-2017-000145 MA-Auto USA 1138.63 1/1/17 A-2017-000146 GA-Auto USA 32 Southeast GA John Auto USA 33 Northeast PA Mike Auto 479.4 1/1/17 A-2017-000153 PA-Auto USA 34 Southeast VS Frank Auto 624.83 1/1/17 A-2017-000154 MS-Auto 1018.12 1/1/17 A-2017-000166 PA-Auto USA 35 Northeast PA Mike Auto 36 Northeast auto 598.54 1/1/17 A-2017-000168 NJ-Auto USA NJ Fred 37 EL John Auto 424.96 1/1/17 A-2017-000170 FL-Auto USA USA 38 Northeast WV Mike Auto 530.8 1/1/17 A-2017-000178 WV-Auto USA 39 West WA Peter Disability 329.38 1/1/17 D-2017-000008 WA-Disability USA 40 New England RI Crissy Disability 164.92 1/1/17 D-2017-000009 RI-Disability USA 41 Northeas Matt Disability 456.24 1/1/17 D-2017-000015 DC-Disability USA 42 Southeast Frank Disability 238.28 1/1/17 D-2017-000019 MS-Disability LISA 43 Midwest Dave Disability 334.05 AT!qESIO 3N OZOO00 2TOZ-0 ZT/T/T MainData VariableCostPct + + 100% ReadySafetyCoverInsurance-Data-SetA Q . Search Sheet Home Insert Page Layout Formulas Data Review View '+ Share X Cut Calibri (Body) * 11 . A- A Wrap Text General 2 AutoSum Copy Fill Paste Format E Merge & Center $ ~ % 2 60 .0 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear Filter B2 X V fx 0.58 A C D E F G H I J K L M N P Q R S T U V W X 1 State Type Variable Cost Percentage 2 AL-Auto 0.58 AL-Disability 0.3 4 AL-Home 0.22 5 AL-Life 0.09 6 AL-Professional 0.29 7 AR-Auto 0.54 B AR-Disability 0.27 9 AR-Home 0.26 10 AR-Life 0.11 11 AR-Professional 0.31 12 AZ-Auto 0.61 13 AZ-Disability 0.29 14 AZ-Home 0.18 15 AZ-Life 0.12 16 AZ-Professional 0.31 17 CA-Auto 0.53 18 CA-Disability 0.29 19 CA-Home 0.19 20 CA-Life 0.12 21 CA-Professional 0.17 22 CO-Auto 0.58 23 CO-Disability 0.28 24 CO-Home 0.23 25 CO-Life 0.12 26 CO-Professional 0.28 27 CT-Auto 0.51 28 CT-Disability 0.3 29 CT-Home 0.21 30 CT-Life 0.13 31 CT-Professional 0.27 32 DC-Auto 0.47 33 DC-Disability 0.26 34 DC-Home 0.25 35 DC-Life 0.11 36 DC-Professional 0.18 37 DE-Auto 0.38 38 DE-Disability 0.27 39 DE-Home 0.19 40 DE-Life 0.1 41 DE-Professional 0.28 42 FL-Auto 0.45 43 FL-Disability 0.32 44 FL-Home 0.22 MainData VariableCostPct + Ready + 100%
