Frequency calculations - four versions with partly different results.

Brirkshied 0 Reputation points
2026-11-18T14:17:04.7066667+00:00

For the calculation of the age structure, I have placed different calculation methods (FREQUENCY(), IFS, PivotTables) next to each other, see Appendix. I noticed that the results are sometimes different. How can that be? Which version would be the correct one or where is the error? Thank you very much for your input.

Microsoft 365 and Office | Excel | For business | Windows
0 comments No comments

1 answer

Sort by: Newest
  1. BabybooHN 4,310 Reputation points Independent Advisor
    2026-11-18T14:38:35.54+00:00

    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 <30 instead of <=30 places 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.

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.