data in Range N$6$: Y$43$ describes NFL quarterback $($QB$)$ statistics for the $2013$ season. Write different formulas for the following questions. Name Team G QBRat Comp Att Pct Yds Y$/$G Y$/$A TD Int

Nick Foles PHI $13119\mathrm{.}2203317642891222\mathrm{.}49\mathrm{.}1272$

$1)$ Using Lookup functions Peyton ManningDEN $16115\mathrm{.}145065968\mathrm{.}35477342\mathrm{.}38\mathrm{.}35510$

QBRat Formulatext of column C Josh McCownCHI $810914922466\mathrm{.}51829228\mathrm{.}68\mathrm{.}2131$

Ben Roethlisberger Philip RiversSDG $16105\mathrm{.}537854469\mathrm{.}54478279\mathrm{.}98\mathrm{.}23211$

Colin Kaepernick Aaron RodgersGNB $9104\mathrm{.}919329066\mathrm{.}62536281\mathrm{.}88\mathrm{.}7176$

Sam Bradford Drew BreesNOR $16104\mathrm{.}744665068\mathrm{.}65162322\mathrm{.}67\mathrm{.}93912$

Matt Ryan Russell WilsonSEA $16101\mathrm{.}225740763\mathrm{.}13357209\mathrm{.}88\mathrm{.}2269$

Jay Cutler Tony RomoDAL $1596\mathrm{.}734253563\mathrm{.}93828255\mathrm{.}27\mathrm{.}23110$

Alex Smith Ben RoethlisbergerPIT $169237558464\mathrm{.}24261266\mathrm{.}37\mathrm{.}32814$

Andy Dalton Colin KaepernickSFO $1691\mathrm{.}624341658\mathrm{.}43197199\mathrm{.}87\mathrm{.}7218$

Cam Newton Sam BradfordSTL $790\mathrm{.}915926260\mathrm{.}716872416\mathrm{.}4144$

Matt Ryan ATL $1689\mathrm{.}643965167\mathrm{.}44515282\mathrm{.}26\mathrm{.}92617$

$2)$ Using Match Function to return the row and column of the selected Jay Cutler CHI $1189\mathrm{.}222435563\mathrm{.}12621238\mathrm{.}37\mathrm{.}41912$

Alex Smith KAN $1589\mathrm{.}130850860\mathrm{.}63313220\mathrm{.}96\mathrm{.}5237$

row number Formulatext of column C column number Formulatext of column F Andy DaltonCIN $1688\mathrm{.}836358661\mathrm{.}94293268\mathrm{.}37\mathrm{.}33320$

Robert Griffin III QBRat Cam NewtonCAR $1688\mathrm{.}829247361\mathrm{.}73379211\mathrm{.}27\mathrm{.}12413$

Christian Ponder Pct Tom Brady NWE $1687\mathrm{.}338062860\mathrm{.}54343271\mathrm{.}46\mathrm{.}92511$

Jason Campbell Y$/$G Andrew LuckIND $168734357060\mathrm{.}23822238\mathrm{.}96\mathrm{.}7239$

Chad Henne Y$/$A Matthew StaffordDET $1684\mathrm{.}237163458\mathrm{.}54650290\mathrm{.}67\mathrm{.}32919$

Tony Romo TD Mike GlennonTAM $1383\mathrm{.}924741659\mathrm{.}42608200\mathrm{.}66\mathrm{.}3199$

Russell Wilson Comp Carson PalmerARI $1683\mathrm{.}936257263\mathrm{.}34274267\mathrm{.}17\mathrm{.}52422$

Robert Griffin IIIWAS $1382\mathrm{.}227445660\mathrm{.}13203246\mathrm{.}471612$

Ryan FitzpatrickTEN $1182217350622454223\mathrm{.}171412$

$3)$ Using combined Index and Match function to return results Ryan TannehillMIA $1681\mathrm{.}735558860\mathrm{.}43913244\mathrm{.}66\mathrm{.}72417$

Formulatext of column D Matt CasselMIN $981\mathrm{.}615325460\mathrm{.}21807200\mathrm{.}87\mathrm{.}1119$

Robert Griffin III QBRat Kellen ClemensSTL $1078\mathrm{.}814224258\mathrm{.}71673167\mathrm{.}36\mathrm{.}987$

Christian Ponder Pct Case KeenumHOU $878\mathrm{.}213725354\mathrm{.}21760220796$

Jason Campbell Y$/$G Christian PonderMIN $977\mathrm{.}915223963\mathrm{.}61648183\mathrm{.}16\mathrm{.}979$

Chad Henne Y$/$A EJ Manuel BUF $1077\mathrm{.}718030658\mathrm{.}81972197\mathrm{.}26\mathrm{.}4119$

Tony Romo TD Jason CampbellCLE $976\mathrm{.}918031756\mathrm{.}82015223\mathrm{.}96\mathrm{.}4118$

Russell Wilson Comp Chad HenneJAC $1576\mathrm{.}530550360\mathrm{.}63241216\mathrm{.}16\mathrm{.}41314$

Joe Flacco BAL $1673\mathrm{.}1362614593912244\mathrm{.}56\mathrm{.}41922$

Matt SchaubHOU $107321935861\mathrm{.}223102316\mathrm{.}51014$

Q$3$ Using the data in "Data" Worksheet and return following information Brandon WeedenCLE $870\mathrm{.}314126752\mathrm{.}81731216\mathrm{.}46\mathrm{.}599$

Results Formula Eli ManningNYG $1669\mathrm{.}431755157\mathrm{.}53818238\mathrm{.}66\mathrm{.}91827$

$1)$ What is the average cost price of "small business" customer type? Terrelle PryorOAK $1169\mathrm{.}115627257\mathrm{.}41798163\mathrm{.}56\mathrm{.}6711$

$2)$ What is the total sales of "high" priority order? Geno SmithNYJ $1666\mathrm{.}524744355\mathrm{.}83046190\mathrm{.}46\mathrm{.}91221$

$3)$ What is the average sales when order date is after $1/1/2015?$

$4)$ What is the number of orders when order date is before $1/1/2015?$

$5)$ What is the average retail price sold by "Yvette Biti"?

$6)$ What is the total sales to customer located in Sydney?

$7)$ What is the number of orders to customer located in Sydney?

Q$4$ Using Pivot Table and Pivot charts to create an interactive dashboard for the following three information.

$1)$ The sales of each account manager by different customer type, using year as the slicer

$2)$ The total cost $($cost price $*$ order quantity $-$ calculate this using pivot field calculation$)$ of each ship mode

$3)$ Total sales by time line $($month$/$year$)$ for different product category

Answer rating: 100% (QA)

1 Average cost price ... View the full answer