Home / Statistical Tools / Analysis Tools / Scatterplot / How-To / Count Frequency
Count Frequency¶
When your paired X/Y data is pre-aggregated with counts, use a Count column as the frequency. Quantum XL expands the dataset by the count value, weighting the regression so that frequently observed data points have more influence on the fitted line.
Goal¶
Create a scatter plot with weighted regression from pre-aggregated hours-vs-output data.
Sample Data¶
Press Copy for Excel, then paste the table into a blank worksheet.
| Hours | Output | Frequency |
|---|---|---|
| 2.0 | 15.3 | 5 |
| 4.0 | 28.7 | 8 |
| 6.0 | 42.1 | 12 |
| 8.0 | 55.8 | 10 |
| 10.0 | 68.4 | 7 |
| 3.0 | 21.5 | 6 |
| 5.0 | 35.2 | 9 |
| 7.0 | 49.0 | 11 |
| 9.0 | 62.3 | 4 |
| 1.0 | 8.1 | 3 |
There are 10 rows with frequencies totaling 75 observations. The frequency column tells Quantum XL how many times each X/Y pair was observed. Points with higher frequency have more influence on the regression line.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. The header row lands in row 1 and the 10 data rows in rows 2 through 11.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Scatter Plot.
-
Select your data
Select cells A1:C11 (the header row plus all 10 data rows).
-
Configure the analysis
In the Scatter Plot dialog:
- X Columns: "Hours" should be checked
- Y Columns: "Output" should be checked
- Frequency Data: Check "Frequency"
- Regression: "Linear" should be selected
Click Finish to generate the chart.
Result¶
Quantum XL creates a scatter plot of Hours vs Output with a weighted linear regression line. The count in the statistics shows 75 (the expanded total), not 10 rows. The regression coefficients and R² are computed on the frequency-weighted data, giving more influence to data points that were observed more frequently.