$9.$ State Sales Tax for Online Purchases. For online purchases, the location of the buyer and the location of the business $($seller$)$ determine if state sales tax must be collected at the time of the sale. The rule for collecting sales tax is that if the business has a physical presence in a state, they must collect applicable sales taxes from online customers who reside in that state. Breckenridge Fabrics sells fabric online and has distribution centers in California, Florida, New Jersey, Ohio, and Texas. The file salestax contains sales data on $279$ purchases. For each purchase the following are provided: Order Number, Buyer State, Supplier State $($state of the distribution center that supplied the goods$)$ and the Sale Amount $($in dollars$).$ Also, in the file salestax is a state tax table that gives the applicable sales tax rate for each of the $50$ states and the District of Columbia. LO $2,7$

a$.$ For each of the $279$ purchases, use the XLOOKUP and IF functions to determine if sales tax must be collected and if so$,$ how much. Round the sales tax due to two decimal places using the ROUND function. What is the total amount of state sales taxes collected from these $279$ purchases?

b$.$ What proportion of these purchases had to pay sales tax?

c$.$ What is the total amount of these purchases $($including any sales tax$)?$

Data

$($Order # Buyer State Supplier State Sale Amount State State Sales Tax Rate$)$

$189$ AK TX $$90$ AL $4\mathrm{.}00\%$

$890$ AK TX $$141$ AK $0\mathrm{.}00\%$

$375$ AK TX $$125$ AZ $5\mathrm{.}60\%$

$641$ AL TX $$198$ AR $6\mathrm{.}50\%$

$773$ AL TX $$113$ CA $7\mathrm{.}25\%$

$675$ AL TX $$78$ CO $2\mathrm{.}90\%$

$297$ AL TX $$182$ CT $6\mathrm{.}35\%$

$223$ AL TX $$198$ DC $6\mathrm{.}00\%$

$976$ AR TX $$171$ DE $0\mathrm{.}00\%$

$155$ AR TX $$100$ FL $6\mathrm{.}00\%$

$847$ AZ CA $$79$ GA $4\mathrm{.}00\%$

$248$ AZ CA $$194$ HI $4\mathrm{.}00\%$

$305$ AZ CA $$89$ ID $6\mathrm{.}00\%$

$653$ AZ CA $$164$ IL $6\mathrm{.}25\%$

$386$ AZ CA $$89$ IN $7\mathrm{.}00\%$

$138$ AZ CA $$144$ IA $6\mathrm{.}00\%$

$941$ AZ CA $$194$ KS $6\mathrm{.}50\%$

$976$ DE NJ $$83$ KY $6\mathrm{.}00\%$

$238$ DE NJ $$200$ LA $4\mathrm{.}45\%$

$740$ DE NJ $$178$ ME $5\mathrm{.}50\%$

$361$ DE NJ $$150$ MA $6\mathrm{.}25\%$

$892$ DE NJ $$133$ MD $6\mathrm{.}00\%$

$269$ DE NJ $$157$ MI $6\mathrm{.}00\%$

$197$ DE NJ $$104$ MN $6\mathrm{.}875\%$

$600$ CA CA $$170$ MS $7\mathrm{.}00\%$

$469$ CA CA $$153$ MO $4\mathrm{.}225\%$

$459$ CA CA $$151$ MT $0\mathrm{.}00\%$

$179$ CO CA $$140$ NC $4\mathrm{.}75\%$

$825$ CO CA $$149$ ND $5\mathrm{.}00\%$

$477$ CO CA $$157$ NH $0\mathrm{.}00\%$

$192$ CO CA $$111$ NJ $6\mathrm{.}625\%$

$885$ CO CA $$158$ NM $5\mathrm{.}125\%$

$940$ CO CA $$164$ NY $4\mathrm{.}00\%$

$570$ CT NJ $$83$ BE $5\mathrm{.}50\%$

$792$ CT NJ $$112$ NV $6\mathrm{.}85\%$

$364$ CT NJ $$140$ OH $5\mathrm{.}75\%$

$794$ CT NJ $$136$ OK $4\mathrm{.}50\%$

$554$ CT NJ $$81$ OR $0\mathrm{.}00\%$

$740$ DC NJ $$198$ PA $6\mathrm{.}00\%$

$115$ DC NJ $$112$ RI $7\mathrm{.}00\%$

$774$ DC NJ $$119$ SC $6\mathrm{.}00\%$

$313$ DC NJ $$140$ SD $4\mathrm{.}50\%$

$489$ DC NJ $$169$ TN $7\mathrm{.}00\%$

$747$ DC NJ $$81$ TX $6\mathrm{.}25\%$

$329$ DE NJ $$161$ UT $6\mathrm{.}10\%$

$744$ DE NJ $$189$ VA $5\mathrm{.}30\%$

$279$ DE NJ $$114$ VT $6\mathrm{.}00\%$

$916$ DE NJ $$119$ WV $6\mathrm{.}00\%$

$666$ DE NJ $$152$ WA $6\mathrm{.}50\%$

$155$ DE NJ $$175$ WI $5\mathrm{.}00\%$

$638$ FL FL $$157$ WY $4\mathrm{.}00\%$

$447$ FL FL $$136$

$698$ FL FL $$95$

$610$ FL FL $$194$

$984$ GA FL $$93$

$841$ GA FL $$87$

$638$ GA FL $$111$

$695$ GA FL $$176$

$705$ GA FL $$181$

$572$ HI CA $$92$

$856$ HI CA $$158$

$568$ HI CA $$135$

$869$ HI CA $$84$

$345$ IA OH $$157$

$673$ IA OH $$127$

$379$ IA OH $$194$

$906$ IA OH $$84$

$782$ ID OH $$193$

$373$ ID OH $$75$

$808$ ID OH $$134$

$483$ ID OH $$179$

$435$ ID OH $$186$

$633$ ID OH $$129$

$213$ IL OH $$133$

$891$ IL OH $$162$

$995$ IL OH $$194$

$579$ IN OH $$123$

$948$ IN OH $$158$

$539$ IN OH $$199$

$440$ IN OH $$170$

$365$ IN OH $$174$

$111$ IN OH $$106$

$601$ KS TX $$123$

$939$ KS TX $$143$

$948$ KS TX $$176$

$464$ KS TX $$87$

$665$ KS TX $$150$

$285$ KS TX $$189$

$657$ KY OH $$179$

$531$ KY OH $$120$

$182$ KY OH $$81$

$139$ KY OH $$89$

$509$ KY OH $$90$

$957$ LA TX $$139$

$600$ LA TX $$118$

$146$ LA TX $$107$

$197$ LA TX $$85$

$680$ LA TX $$192$

$511$ LA TX $$132$

$166$ LA TX $$97$

$304$ LA TX $$188$

$949$ MA NJ $$101$

$624$ MA NJ $$130$

$260$ MA NJ $$175$

$195$ MA NJ $$105$

$622$ MA NJ $$132$

$663$ MD NJ $$75$

$919$ MD NJ $$140$

$344$ MD NJ $$150$

$621$ MD NJ $$151$

$493$ MD NJ $$146$

$761$ MD NJ $$178$

$883$ MD NJ $$88$

$631$ MD NJ $$91$

$739$ MD NJ $$143$

$343$ ME NJ $$196$

$747$ ME NJ $$75$

$806$ MI OH $$183$

$318$ MI OH $$176$

$652$ MI OH $$182$

$275$ MN OH $$149$

$262$ MN OH $$96$

$690$ MN OH $$143$

$282$ MN OH $$132$

$953$ MN OH $$163$

$351$ MO OH $$128$

$638$ MO OH $$81$

$368$ MO OH $$132$

$291$ MO OH $$168$

$295$ MO OH $$144$

$790$ MO OH $$153$

$977$ MO OH $$144$

$852$ MO OH $$169$

$193$ MS FL $$146$

$545$ MS FL $$76$

$471$ MS FL $$152$

$756$ MS FL $$199$

$758$ MS FL $$117$

$884$ MS FL $$185$

$450$ MT CA $$133$

$450$ MT CA $$104$

$224$ MT CA