Question

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.

Answer 1

13. High Annual Sales =IF(A2>AVERAGE($A$2:$A$22),"Blue Chip","Basic")

15. Order Volume   =IF(B2>AVERAGE($B$2:$B$22),"High Volume","Low Volume")

Note: You have to replace 22 with last cell no. of high annual sales/order volume column.

For Conditional Formatting, use "Highlight Cell rules" > "Text that contains" > enter text "with" custom format and use font and fill tab to choose color as mentioned.