Question

How would I Calculate the Average Quarterly Revenue for (AQR) for the Label indicated car class for each of the four car classes. Using the average function and name range (AE, AH, AP or AS) ONLY NOT a range of cells. Ensure each of four averages is Currency format with two decimal places preceded by a $ with no spaces between the $ and the first number. I keep getting an error column K. Please help

AutoSave o B Copy of Campbell Theodora Pioject1 cel File Home insert Draw Page Layout Forinudas Data Revire View Developer Help p Search Pawer Prot X Cu -11A A | Calibr 22 Wrap Test D Copy ite General Norrmal Bed A E Merge & Center 8IU- Format Painter Conditional Format as Calculation Fermatting Table- Cherk Cell Ciphoard Font Alignment Number Styles L28 A C Location CaCl RevenudNunCa AvgRe Airport Airpert G HI Yea Quar 2016 01 1 M Average Quartely Revnue per Economy Car (Airpart) Average Quartely Revoue per tytard Car (Airport) Average Quartely Revsue per Premum Car (Airport) Average Quarlely Revnoe per SUV (Airport) 2 Economy $459.336 3,167 145 04 3.202 150 26 5600 $ 18283 3.243 $ 151.41 2092 179 86 3086 S 107 11 4.603 155 64 3454 175 70 3 2017 01 Economy Economy Economy Economy 5509,946 2018 01 4 Nrport $1,023.831 2016 02 Arport Airport $491 016 6 2017 02 $376 277 7 2018 02 Airport Economy $608 290 0 2016 Q3 Airport Economy $717 204 9 2017 03 Awport Economy $606 807 TO 2016 04 Airport Economy $625 790 3.995 $ 156 64 11 2017 04 Airport Economy $937.962 5.076 104.78 121 2018 01 2017 01 Hybnd Airport 2.333 157 57 5367,600 13 Airport Hybrid $577.995 3415 $ 1069 25 14 2018 01 Airport $603.576 Hybrid 3778 18358 15 2016 02 Airport Hybrid Hybrid 1.926 $151 00 3,546 172.50 3.747 188 40 3,002 $164 62 5310 194 16 2017 02 Arpon $612.001 17 2018 02 Airport Hybrid $705,921 13 2016 03 Arport Hyond Hybrid $494,170 19 2017 03 Airport S672 993 3816 $ 176.36 20 2016 04 Airport Hybid $648.257 3.863 $ 16781 21 2017 04 Airport Hybrid $552,851 3.027 $ 182 64 2269 $ 161.82 3.321 174 12 3,689 188 15 22 2016 01 Airport Premium $367,179 $578 244 23 2017 O1 Airport Premium 24 2018 01 Airport Premium $694 093 25 2016 02 Airport Premium $309 021 1,878 $164.97 26 2017 02 Airpar 5612 700 Pramium 3,455 177 34 27 I 2018 02 Airport Premium 5706.215 3.693 191.23 2016 03 Airport Premium $493 808 2.019 169 17 29 2017 03 Arport Premium $872,058 3.773 $ 178.28 30 2016 04 Arport Premium $048 680 3782 $ 171.52 2995 104 35 2417 152 19 31 2017 04 Airport Premium $552,136 32 2016 01 Airport SUV $307.850 33 SUV 2017 01 Alrport $578 023 3520 $ 154.21 34 2010 Q1 Airport SUV $604 300 3.880 $ 178 501 35 2016 02 Airport SUV 5309,778 $612,386 1,900 $ 157 33 36 2017 Q2 Airport SUV 3676 S 166.50 37 2018 02 Airport SUV $706.043 3,803 18133 38 2016 03 Arport SUV 3,110 $ 158 62 3,081 168 93 3,993 1557 89 3.140 175 00 $493304 2017 03 $672.522 39 Airport SUV 40 2016 04 Airpoit Airport BUV $631.250 41 2017 04 SUv $552.328 42 43 44 45 Analysis by Data Alrport Sorted Airport 1 Rraty

Answer 1

Here is the formula version

H Average Quarterly Revenue per Economy Car (Airport) Average Quarterly Revenue per Hybrid Car (Airport) Average Quarterly Revenue per Premium Car (Airport) Average Quarterly Revenue per SUV (Airport) =AVERAGEIF($D$2:$D$41, D2,$G$2:$G$41) =AVERAGEIF($D$2:$D$41,D12,$G$2:$G$41) =AVERAGEIF($D$2:$D$41,D22,$G$2:$G$41) =AVERAGEIF($D$2:$D$41,D32,$G$2:$G$41) A B C D E F G 1 Year Quarter Location CarClass Revenue NumCar AvgRev 2 2016 Q1 Airport Economy 459336 3167 =E2/F2 3 2017 Q1 Airport Economy 509946 3202 =E3/F3 4 2018 Q1 Airport Economy 1023831 5600 =E4/F4 5 2016 Q2 Airport Economy 491016 3243 =E5/F5 6 2017 Q2 Airport Economy 3762772092 =E6/F6 7 2018 Q2 Airport Economy 608290 3086 =E7/F7 8 2016 Q3 Airport Economy 717204 4608 =E8/F8 9 2017 Q3 Airport Economy 606867 3454 =E9/F9 10 2016 Q4 Airport Economy 625790 3995 =E10/F10 11 2017 04 Airport Economy 937962 5076 =E11/F11 12 2016 01 Airport Hybrid 3676002333 =E12/F12 13 2017 Q1 Airport Hybrid 5779953415 =E13/F13 14 2018 Q1 Airport Hybrid 693576 3778 =E14/F14 15 2016 Q2 Airport Hybrid 310194 1926 =E15/F15 16 2017 02 Airport Hybrid 612001 3546 =E16/F16 17 2018 Q2 Airport Hybrid 705921 3747 =E17/F17 18 2016 Q3 Airport Hybrid 494178 3002 =E18/F18 19 2017 Q3 Airport Hybrid 672993 3816 =E19/F19 20 2016 Q4 Airport Hybrid 648257 3863 =E20/F20

Here is the result

H L Average Quarterly Revenue per Economy Car (Airport) Average Quarterly Revenue per Hybrid Car (Airport) Average Quarterly Revenue per Premium Car (Airport) Average Quarterly Revenue per SUV (Airport) $168.83 $172.39 $176.10 $166.21 8 9 10 A B C D E F G 1 Year Quarter Location CarClass Revenue NumCar AvgRev 2 2016 01 Airport Economy $ 459,336 3167 $ 145.04 3 2017 Q1 Airport Economy $ 509,946 3202 $ 159.26 4 2018 Q1 Airport Economy $ 1,023,831 5600 $ 182.83 5 2016 Q2 Airport Economy $ 491,016 3243 $ 151.41 6 2017 Q2 Airport Economy $ 376,277 2092 $ 179.86 7 2018 Q2 Airport Economy $ 608,290 3086 $ 197.11 2016 Q3 Airport Economy $ 717,204 4608 $ 155.64 2017 Q3 Airport Economy $ 606,867 3454 $ 175.70 2016 Q4 Airport Economy $ 625,790 3995 $ 156.64 2017 04 Airport Economy $ 937,962 5076 $ 184.78 2016 01 Airport Hybrid $ 367,600 2333 $ 157.57 13 2017 Q1 Airport Hybrid $ 577,995 3415 $ 169.25 14 2018 01 Airport Hybrid $ 693,576 3778 $ 183.58 15 2016 Q2 Airport Hybrid $ 310,194 1926 $ 161.06 2017 Q2 Airport Hybrid $ 612,001 3546 $ 172.59 17 2018 Q2 Airport Hybrid $ 705,921 3747 $ 188.40 18 2016 Q3 Airport Hybrid $ 494,178 3002 $ 164.62 19 2017 Q3 Airport Hybrid $ 672,993 3816 $ 176.36 20 2016 Q4 Airport Hybrid $ 648,257 3863 $ 167.81 11