Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Charting Troubles 2d Pie

Why is charting in Apple Numbers so obtuse?


Take for example:


Category

$

Entertainment

-33.53

Grocery

-10.3

Grocery

-64.83

Entertainment

-52.63

Payment

1600

Sports Leisure

-60

Grocery

-19.35

Grocery

-27.02

Health

-78.46

Home Maintenance

-32.46

Online Services

-10.63

Fuel

-118.2

Grocery

-9.68

Eating Out

-11.42

Online Services

-12.99

Online Services

-63.41

Entertainment

-29.08

Grocery

-72.65

Health

-23.21

Online Shopping

-36.28

Online Shopping

-76.53

Payment

420

User uploaded file

Very simple, takes 2s to create a chart in Google Sheets or Excel.


In Apple Numbers, I honestly have not a clue.


I create a 2D pie chart and only get a blue blob. I start selecting data references and instead of grouping the categories, it lists them separate.


I think it would be quicker to create a chart with pen and paper than this convoluted "Facebook" like hidden top secret broken way of creating charts in Numbers.


Or is there a simple button somewhere that lets me "unbreak" charting?


Thank you!


Mars

iMac (Retina 5K, 27-inch, Late 2015), macOS Sierra (10.12.1), 'stickers'

Posted on

Reply
Question marked as Solved
Answer:
Answer:

That would have been Numbers '09 (version 2.x), and the feature was "Categories".


Categories was a 'display only' feature which grouped the rows of a table using the values in a specified column, separated the groups with "Category rows", in which results of several calculations on data in that category could be done, and the results displayed.


Unfortunately, the category results were 'read only.' They were displayed in the Category Rows (which existed only when the feature was engaged), and could not be referenced by formulas, or otherwise used in further calculations (including user defined or system calculations needed to produce charts—chart data requirements were much the same as they are in the more current versions).


I remember looking at it when it came out, but don't recall finding the feature particularly useful with the shortcomings mentioned above.


YMMV, of course.


Regards,

Barry

Posted on

Question marked as Helpful

Mar 10, 2018 11:49 AM in response to marsofearth In response to marsofearth

Hi Mars,


"Or is there a simple button somewhere that lets me "unbreak" charting?"


I don't know how you'd go about 'unbreaking' something that's not broken, but you might find the Charting Basics template useful:

User uploaded file



Numbers will chart the data you provide. You need to aggregate the data, as I've done below.


Numbers provides two types of charts: x,y scatter charts, where both the x and y axes are 'value' axes (the 'bubble chart also fits into this group), and Category charts, where the y axis is a value axis, and the x axis is a 'category' axis.


Y values are always numerical data, and MUST be recorded in a non-header row or column.

X values for category charts are text and must be recorded in a Header row or Header column.


The Pie chart is a 'category' chart—each category is assigned a colour (from a severely limited set), and the 'y' value, converted to a percentage, determines the angle of the wedge representing that category.


The category chart below is shown as it was produced by selecting the aggregated data in the selected cells of the table at the right, then clicking the Charts button and selecting 2D Pie chart. No further editing was done to the chart.

User uploaded file

Your data (Thank you for pasting it into your message rather than using a screen shot) is in the table to the left.

I Used SUMIF, with the result multiplied by-1 to produce a set of positive values for the category totals in the table used to feed the chart. Column A is a Header column of each of the tables. Row 1 is a Header row.


Regards,

Barry


PS: If you see "New", but not "New from Template Chooser" in the file menu, press and hold the option key while making the choice. You can also open the Template Chooser by pressing option-command-N.

B.

Question marked as Helpful

Mar 10, 2018 4:49 PM in response to marsofearth In response to marsofearth

Hi Mars,


With this short an example, I pulled the distinct values (excluding "Payment") manually.


Another method would be to select all cells in the column, open the Format inspector, choose Cell, and set the data format to Pop-Up Menu. This converts every cell in the column to a pop-up menu cell containing a pop-up list of all the distinct values in the column. Copy one of these cells, Paste it into as many cells in column A of the second table as you'll need, then set one to each category you want included on the chart.


For lists with more categories, or more entries, I'd use a formula to create an index column marking the first occurrence of each category, then filter the table to show only those rows, select the visible cells and copy, then paste into the category column of the second table. Example below:


Your data table with added index column ('filter'). Filter rule shown to the right.

User uploaded file

Column C ('filter') contains this formula, entered in C2, then filled down to C23:


C2: IF(COUNTIF(A$1:A2,A2)=1,MAX(C$1:C1)+1,"hide")


COUNTIF counts the occurrences of the value in 'this row' of column A in cells A1 to 'this row' of A.

IF the count is 1, IF calls MAX, which gets the MAXimum value in column C from C1 to the cell above 'this cell' and adds 1 to that result.

If the count is not 1, IF places the text "hide" in 'this cell'.


The values in this column can be used to filter the table using the Filter Rule shown to the right, leaving only the rows containing the first occurrence of each category visible and ready to be copied, or the numbers in this column can be used with MATCH and INDEX functions to copy the first occurrences directly to another table.


Here's the same table with the filter turned on:

User uploaded file


Regards,

Barry

There’s more to the conversation

Read all replies

Page content loaded

Question marked as Helpful

Mar 10, 2018 11:49 AM in response to marsofearth In response to marsofearth

Hi Mars,


"Or is there a simple button somewhere that lets me "unbreak" charting?"


I don't know how you'd go about 'unbreaking' something that's not broken, but you might find the Charting Basics template useful:

User uploaded file



Numbers will chart the data you provide. You need to aggregate the data, as I've done below.


Numbers provides two types of charts: x,y scatter charts, where both the x and y axes are 'value' axes (the 'bubble chart also fits into this group), and Category charts, where the y axis is a value axis, and the x axis is a 'category' axis.


Y values are always numerical data, and MUST be recorded in a non-header row or column.

X values for category charts are text and must be recorded in a Header row or Header column.


The Pie chart is a 'category' chart—each category is assigned a colour (from a severely limited set), and the 'y' value, converted to a percentage, determines the angle of the wedge representing that category.


The category chart below is shown as it was produced by selecting the aggregated data in the selected cells of the table at the right, then clicking the Charts button and selecting 2D Pie chart. No further editing was done to the chart.

User uploaded file

Your data (Thank you for pasting it into your message rather than using a screen shot) is in the table to the left.

I Used SUMIF, with the result multiplied by-1 to produce a set of positive values for the category totals in the table used to feed the chart. Column A is a Header column of each of the tables. Row 1 is a Header row.


Regards,

Barry


PS: If you see "New", but not "New from Template Chooser" in the file menu, press and hold the option key while making the choice. You can also open the Template Chooser by pressing option-command-N.

B.

Mar 10, 2018 11:49 AM

Reply Helpful (1)
Question marked as Helpful

Mar 10, 2018 4:49 PM in response to marsofearth In response to marsofearth

Hi Mars,


With this short an example, I pulled the distinct values (excluding "Payment") manually.


Another method would be to select all cells in the column, open the Format inspector, choose Cell, and set the data format to Pop-Up Menu. This converts every cell in the column to a pop-up menu cell containing a pop-up list of all the distinct values in the column. Copy one of these cells, Paste it into as many cells in column A of the second table as you'll need, then set one to each category you want included on the chart.


For lists with more categories, or more entries, I'd use a formula to create an index column marking the first occurrence of each category, then filter the table to show only those rows, select the visible cells and copy, then paste into the category column of the second table. Example below:


Your data table with added index column ('filter'). Filter rule shown to the right.

User uploaded file

Column C ('filter') contains this formula, entered in C2, then filled down to C23:


C2: IF(COUNTIF(A$1:A2,A2)=1,MAX(C$1:C1)+1,"hide")


COUNTIF counts the occurrences of the value in 'this row' of column A in cells A1 to 'this row' of A.

IF the count is 1, IF calls MAX, which gets the MAXimum value in column C from C1 to the cell above 'this cell' and adds 1 to that result.

If the count is not 1, IF places the text "hide" in 'this cell'.


The values in this column can be used to filter the table using the Filter Rule shown to the right, leaving only the rows containing the first occurrence of each category visible and ready to be copied, or the numbers in this column can be used with MATCH and INDEX functions to copy the first occurrences directly to another table.


Here's the same table with the filter turned on:

User uploaded file


Regards,

Barry

Mar 10, 2018 4:49 PM

Reply Helpful (1)

Mar 10, 2018 4:54 PM in response to Barry In response to Barry

Wow! Okay.


Sorry I guess I have been used to using google sheets where you do not need to do all this extra rigamaroll.

I thought Numbers would be easy.


I like using Numbers because it's "Pretty" and I love being able to have multiple tables on a single sheet, but as far as I can tell, charting is plain broken. Aggregating is a pretty basic attribute for any spreadsheet/charting application.


I'll just do all the crunching on Google Sheets, and copy paste into Numbers for making pretty reports.


Thanks for the heads up! Really appreciate it!


Cheers!

Mar 10, 2018 4:54 PM

Reply Helpful

Mar 10, 2018 5:19 PM in response to Barry In response to Barry

I just want to also thank you for doing all of this work!


You did a lot here to explain how to chart and aggregate in Numbers. I hope for your sake alone Numbers will gain the ability as it once had, to be able to aggregate automatically on request.


Thank you, really appreciate the hard work you put in here.


Mars

Mar 10, 2018 5:19 PM

Reply Helpful

Mar 11, 2018 12:04 PM in response to Barry In response to Barry

Honestly do not know which version id number.


It was the version before Apple dumbed down iWork apps to have functional equality to its iOS and Web versions.


I used to be able to quickly categorize a table by a selected column. Maybe I am mistaken and getting Google Sheets mixed up, although I am sure I used to be able to quickly aggregate categories within a column in older versions of Numbers.

Mar 11, 2018 12:04 PM

Reply Helpful
Question marked as Solved

Mar 11, 2018 2:57 PM in response to marsofearth In response to marsofearth

That would have been Numbers '09 (version 2.x), and the feature was "Categories".


Categories was a 'display only' feature which grouped the rows of a table using the values in a specified column, separated the groups with "Category rows", in which results of several calculations on data in that category could be done, and the results displayed.


Unfortunately, the category results were 'read only.' They were displayed in the Category Rows (which existed only when the feature was engaged), and could not be referenced by formulas, or otherwise used in further calculations (including user defined or system calculations needed to produce charts—chart data requirements were much the same as they are in the more current versions).


I remember looking at it when it came out, but don't recall finding the feature particularly useful with the shortcomings mentioned above.


YMMV, of course.


Regards,

Barry

Mar 11, 2018 2:57 PM

Reply Helpful
User profile for user: marsofearth

Question: Charting Troubles 2d Pie