Histogram and frequency analysis in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand the distribution of daily sales amounts to identify common sales ranges and frequency.

📊 Data: You have a list of daily sales amounts for the last 12 days.

🎯 Deliverable: Create a frequency distribution table and a histogram chart showing how many days fall into each sales range.

Sample Data

Day	Sales Amount ($)
1	120
2	150
3	90
4	200
5	170
6	130
7	80
8	160
9	110
10	140
11	190
12	100

1

Step 1: Create bins for sales ranges in a new column. Use these bins: 80, 100, 120, 140, 160, 180, 200.

Enter these values in cells D2 to D8: 80, 100, 120, 140, 160, 180, 200

Expected Result

Bins column with values 80, 100, 120, 140, 160, 180, 200 listed vertically.

2

Step 2: Calculate the frequency of sales amounts falling into each bin using the FREQUENCY function.

Select cells E2 to E9, enter formula: =FREQUENCY(B2:B13, D2:D8), then press Ctrl+Shift+Enter to enter as an array formula.

Expected Result

Frequency counts for each bin showing how many sales amounts fall into each range.

3

Step 3: Label the frequency table columns as 'Sales Range' and 'Frequency'.

Enter 'Sales Range' in cell D1 and 'Frequency' in cell E1.

Expected Result

Headers above bins and frequency counts for clarity.

4

Step 4: Create a histogram chart using the frequency table.

Select cells D1:E8, go to Insert > Charts > Column Chart > Clustered Column.

Expected Result

A histogram chart showing sales ranges on the horizontal axis and frequency on the vertical axis.

Final Result

Sales Range | Frequency
80          | 2
100         | 2
120         | 2
140         | 2
160         | 2
180         | 1
200         | 1

Histogram Chart:
[Bar heights correspond to frequencies above, bars labeled by sales ranges]

✓Most sales amounts fall between 80 and 160 dollars.

✓The highest frequency is 2 days for several sales ranges, showing sales are spread evenly across these ranges.

✓There are no sales amounts below 80 or above 200 in the data.

Bonus Challenge

Create dynamic bins that adjust automatically if new sales data is added.

Show Hint

Use the MIN and MAX functions to find the lowest and highest sales amounts, then create bins using formulas that divide this range into equal intervals.