Creating number ranges in a Pivot Table

Question

Level 1

34 points

Creating number ranges in a Pivot Table

In a Numbers pivot table, how can I set up rows to show a number range instead of a row for each and every number?

For example, I have a two column table: 1. # of video clips and 2. Created a merged video

The standard pivot table would give me a row for each and every count of # of video clips. I'd like to instead create ranges (eg 0-5, 6-10, 11-15, etc). I can see how to summarize date fields by week or month. Would love to be able to do that with any numeric data.

Is that possible? I'm using Numbers V 13.0

MacBook Pro 13″, macOS 11.1

Posted on Apr 11, 2023 2:07 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,532 points

Apr 12, 2023 1:31 AM in response to hab1

The ability of Pivot Tables to automatically aggregate by date ranges is a real time saver!

However, I do not think it is possible to do that directly with number values.

There is, though, a way to create the ranges in your data table without too much trouble, something like this:

=IFS(A2≤5," 0-5",A2≤10," 6-10",A2≤15,"11-15")

If you have more ranges, just include the ifs-expression, if-true pairs within the IFS function.

(I left an extra space before 0-5 and 6-5 here in case you might later want to sort on that column).

Then when creating the Pivot Table you can drag the added "range" column into the Rows box instead of the original column.

SG

Reply

Answer 2

hab1 Author

Level 1

34 points

Apr 12, 2023 8:50 AM in response to SGIII

Fantastic suggestion, SG. And I now also learned how to use IFS. I've always used embedded IF statements, which can get really clunky!

Reply

Answer 3

hab1 Author

Level 1

34 points

Apr 12, 2023 1:14 PM in response to hab1

I just noticed another thing you did. To get the ranges to sort in order, you added a space to the single digit ranges. Very clever! I learned three things in one post 😄

Reply