Charting Troubles 2d Pie

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:

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.

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.

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.

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:

Regards,

Barry

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

" I hope for your sake alone Numbers will gain the ability as it once had, to be able to aggregate automatically on request."

Which version had this ability?

Regards,

Barry