Apple Event: May 7th at 7 am PT
Left Excel for Numbers but can't get Numbers charts to display currency AND percentage: just one or the other. How can I set my format options to output both currency and percentage on the same chart? (It's an accounting ledger I'm working.)
MacBook Pro, Mac OS X (10.7.3), iWork '09
g7,
If you post a screen shot of an example or a mock-up, we can probably make a suggestion.
Jerry
Here's an example of what I usually did. The chart contains the tag for data, its numeric value as currency and a percentage of the total. In Numbers, it seems as though I can have numeric or currency or a percentage but a not a combination of them. Hope this helps, I appreciate your interest.
Possible, but not simple options. Here is what you can do:
What I did was calculate the data categories to include the percentages. The expression in B is:
=A&", "&D
where D is:
=C/SUM(C)
Note the setting for displaying the series name in the Inspector.
Here's a similar approach for the amount and the percentage:
Here, the column B expression is:
=C&", "&D
Regards,
Jerry
Jerry,
Great! It works. One thing I did notice with this is that the font is white with shadow, rather than black. Any ideas. Brilliant solution. Thanks again.
To continue with one more comment, I think using all three elments; Name, Amount and Percentage, is rather messy.
Jerry
You general, you may find the shadow in two places, maybe more. You have to hunt down those little rascals and turn them all off.
Sometimes you will find the shadow has been set in the Graphic Inspector, but in this case it's in the Fonts Panel. In some cases, both are set. In this example, I first set the text color to Black and then toggled off the shadow in the Fonts Panel.
Jerry
This is a minor reworking of Jerry's solution to show both the amount (rounded) and the percentage for each category.
The Percentage calculation in column D is redundant, as numbers will calculate this anyway, and will add it to the labels if the "Percentage" format is chasen for the labels.
I've revised calculations in column B to concatenate the Title (from column A) and the amount (from column C), rounded to the nearer whole pound. Between the two elements, I've placed a return character (option-return in the formula) to place the amount on the line below the title. Numbers adds the percentage, and places it on a third line. Chart Inspector settings are as shown.
The labels were selected, and the text weight and colour changed to regular and black respectively, using the Text formatting controls in the Format bar. I didn't discover a way to remove the shadow effect from the label text.
Regards,
Barry
Barry,
Commant-T to view the Fonts Panel, then:
Jerry
Hi Jerry,
Thanks.
I was looking for that. It may have been added to v2.0.4 or v2.1. Mine (2.0.3) shows only the first four buttons
Regards,
Barry
This worked a treat and maintains negtive values from the table in the chart which is superb. Couldn't figure out how to roundup the figure to disregard the decimals. Could you show the formula please? Superb result.
No idea why, but text shadow control isn't showing up in the font panel. I'm using v. 2.1 (436). Any ideas?
g7mcd wrote:
Couldn't figure out how to roundup the figure to disregard the decimals. Could you show the formula please? Superb result.
Sorry. I must have been in a bit of a rush when I posted, and neglected to include the formula. Here it is:
B2: =A&"!£"&ROUND(C,0)
Paste or type the formula into B2, then replace the exclamation point with option-return to force the amount onto a second line.
Regards,
Barry
You're on a roll Barry.
Here's another thing that came up while using charts with these formulae. When I use the data in a pie chart, it's all sweetness and light: name, amount and percentage. BUT, when I use a bar chart and go into inspector, "Axis", Value Axis (Y) and change the format to the percentage, the percentages are weird. Here's what it looks like:
In chart 16, I used the exisiting values (-ve) and in chart 17, I used an ABS() formula to change the values to positive and had the same weird percentages. No idea why, or if there is a solution but I am curious why pie charts are fine, giving the values that I expect but the bar chart goes all wonky. The thing is, the percentages are right except for being 1,000x too high.
You've probably figured out by now that I'm not too smart. I had to look up "concatenate" too. 😝
In a pie chart, the whole 'pie' represents 'all of the data graphed'.
In percent, that's "one hundred per cent", or "one hundred per hundred"
As a fraction, the whole circle represents 100/100, or 1/1 or 1.00 — all of which mean "one whole thing".
Smaller portions are written as fractions of the whole, with the 'whole' fixed at 100 (percent) or 1.00.
So a 20 pence piece represents 20/100 of a pound, which is 20 percent of a pound, or may be written as a decimal as 0.20 pound.
One pound is 1.00 pound, or 100% of a pound.
A 10 pound note is 10.00 pounds, or 1000% of a pound.
And getting into the range of values on your Chart 16, 455 pounds is 455.00 pounds, or 45500% of one pound, which, as you can see, is correctly represented on the chart.
Here's a pair of charts, and the table that feeds them. Same data as in your example, but with the Y axis scale showing the raw data numbers on Chart 1, and the fraction of the total (1146) that each amount represents on the chart 2.
Formatting the Y axis labels to show percent doesnt change the values. The numbers are multiplied by 100, and the % sign is attached to show that these are the number per hundred. For chart 1, where the current scale takes no account of the total, converting to percent doesn't make sense.
For Chart 2, with the current scale, the four bars show (approximately) 0.2, 0.3, 0,4 and 0.1. Altogether, that's 0.2+0.3+0.4+0.1 = 1.0
Converting Chart 2's Y axis values to percents changes the labels on the four non-zero lines to 10, 20, 30 and 40%. The four bars now show 20, 30, 40 and 10 percent. Add those up and you get 100% (which is always the total percentage of 'the whole thing'.
Regards,
Barry
Makes perfect sense. Thanks Barry, I knew I'd asked the right person.
Left Excel for numbers but can't get Numbers charts to display currency AND percentage: just one or the other. How can I set my format options to output both data on the same chart?
