We need calculations for merchandise sold in different weekdays. First, create a column "weekdays" in column "I", based on transaction day in column “A”.
business
Description
- We need calculations for merchandise sold in different weekdays. First, create a column "weekdays" in column "I", based on transaction day in column “A”.
- List weekdays (Monday-Sunday) in range "K6:K12". These weekdays are row headers for a table of calculations. Start from cell “K6” to leave a few rows on top for referenced values.
- Use a formula in cell L6 to calculate total sales for Monday. Reference cells and ranges appropriately and copy this formula down through cell L12. Each cell should calculate total sales of the weekday listed in column K in the same row.
- As a good practice, you will not type any values as a criterion (number or text string) in any of your formulas. Use the current weekdays in column K and type referenced values above the table in rows 1-5, in the same column.
- Use a formula in column “M6” to calculate total sales of 60” TVs in weekdays. 60” TVs are identified in Type column by 60.
- Reference column “M” TV sales numbers in column “N” and use conditional formatting to create data bars in this column. These data bars should show bars only, not numbers.
- In row 6, columns “O” through “U” calculate average sales for each weekday through each channel. List channels in this order on top of the table and reference them: Website, banner, email, North, South, East, and West. Your formulas must copy down the columns to calculate sales for all weekdays. Use the following conditional formatting rules to format these columns:
- Website: 5 arrows directional
- Banner: 5 rating bars
- Email: data bars
- North: color scale
- South: above average
- East: 3 symbol indicators
- West: top 50%
- Use conditional formatting to apply a light green background color to the entire rows in data table (columns A-I), where sales value of the item is more than twice the median sale price of all items. (Excel formula MEDIAN is used).
