60.1K

Verified Solution

Link Copied!

I am confused on how to write the function for the following problems. Based off the excel document provided.

13. In the "High Annual Sales" column create a function to identify customers that have sales greater than the total average sales of all customers for the year. If greater than the total average, the function should return "Blue Chip," otherwise return "Basic." Conditionally format the "Blue Chip" as white text with a blue fill. 14. Insert a new column to the right of "High Annual Sales" and label it "Order Volume." 15. In the "Order Volume" column create a function to identify customers that have more orders than the average of all orders. If the customer has more orders than the average of all customer orders, return a value of "High Volume," otherwise, "Low Volume." Conditionally format the "High" as white text with a green fill. A C D E F Number of Orders Order Volume Order Quality Nested High Annual Sales Total Annual Sales Order Quality IFS 1 $315,044.33 66385 LOW Low 2 $13,444.68 Low 2172 LOW $181,120.24 Low 4 35696 LOW $69,608.15 Low 5 15205 LOW $30,940.35 7833 LOW Low 6 $74,321.18 Low 7 14328 LOW $18,474.15 Low 8 4677 LOW $80,890.34 Low 9 17790 LOW $31,746.70 5057 LOW Low 10 $103,244.26 Low 11 20958 LOW $160,227.13 Low 12 33535 LOW $67,179.39 Low 13 12959 LOW $181,287.73 Low 14 33799 LOW $133,617.26 Low 15 25788 LOW $186,696.56 Low 16 36190 LOW $118,193.46 Low 17 24532 LOW $72,891.83 Low 18 11891 LOW $12,469.92 1992 LOW Low 19 $96,009.15 Low 20 17404 LOW $137,602.28 Low 21 25750 LOW $11,637.34 Low 22 1859 LOW $12,843.90 Low 23 2010 LOW $103,089.33 Low 24 20010 LOW $13,036.14 Low 25 2106 LOW N $340,867.26 Low 26 61779 LOW $138,986.22 26047 LOW Low 27 $20,358.30 Low 28 5154 LOW $120,190.31 23738 LOW Low 29 $64,370.58 Low 30 12422 LOW $74,254.93 Low 31 14899 LOW $278,106.13 Low 32 52875 LOW $17,421.46 Low 33 2842 LOW $96,692.34 Low 34 17590 LOW $49,298.46 Low 35 9594 LOW $78,856.60 Low 36 15256 LOW $30,853.10 Low 37 6691 LOW $91,818.01 Low 38 18391 LOW $70,482.18 Low 39 11294 LOW $168,290.41 Low 40 30265 LOW $42,871.80 Low 41 8636 LOW $36,562.75 Low 42 6832 LOW $12,875.20 2080 LOW Low 43 $108,368.09 19305 LOW Low 44

Answer & Explanation Solved by verified expert

See Answer