A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Dear @Brirkshied
All three methods can give the same result when they use the same source data and identical age-group boundaries. I cannot identify which result is correct without seeing the appendix, but I would check these points first:
- FREQUENCY uses upper limits. For example, bins of 20, 30 and 40 count ages ≤20, >20 to ≤30, >30 to ≤40, plus an additional result for >40. It also ignores blank cells and text, including ages stored as text. FREQUENCY function
- IFS returns the first matching condition. The order matters, and using
<30instead of<=30places someone aged exactly 30 in a different group. IFS function - For the PivotTable, check that its source includes all records, refresh it, remove unintended filters, and confirm that the Values field uses Count, rather than Sum. Also compare its group boundaries with those in the formulas.
As a quick check, inspect people whose ages fall exactly on the boundaries, such as 20, 30 and 40, and compare the totals across all four versions.
Could you attach an anonymized sample workbook, including the age data, formulas, bin limits and PivotTable? I do not see the appendix here. That will let me identify the specific difference rather than guess.