So here's something I was playing around with
in Numbers.
Suppose you have a chart like this where you
have various items with numbers next to them
and you want to create a pie chart.
Let's click on chart here.
I'll create a pie chart.
Since I had this selected it populates it
with all the data.
Notice I've got some very narrow wedges here.
Typically you'd want to group these all together
and then define them as Other.
But there really isn't an option to do that.
But I figured out a way that you could do
it and kind of keep updating things and it
would work.
So let's get rid of this chart here.
I'm going to go back to the table and I left
an extra row here for a reason.
I want to switch that to a footer row and
get a total.
So I'm going to do equals, SUM for sum, click
on the heading there and now I have a total
for all sales, that's going to come in handy.
I'm going to create another table here, and
this table, let's put it next to it, I'm going
to do something similar.
I'm going to do Items and Sales again, I don't
need all those there, and I'm going to do
some formulas.
So I'm going to do equals and I'm just going
to click here where it says apples.
In other words just give me a copy of what's
there.
Then I'm going to do the same thing here.
I could just do equals and then click there
and get that number.
But instead I'm going to use an IF function.
So I'm going to do, IF and I'm going to choose
this cell here, is when divided by this cell
at the bottom here, but I'm going to click
on that cell and say Preserve Row, Preserve
Column.
So I'm going to take this number here and
divide it by that.
That'll give me a percentage.
So if it's greater than 0.1, in other words
10%, then give me that value.
Otherwise give me zero.
That's the function there.
In other words give me the value only if it's
greater than 10% of the total.
So I get 58 there.
Now I'm going select both of these, Copy,
and I'm going to paste them and you see for
oranges it's greater than 10%, for bananas
it's greater than 10%, for peaches, pears,
and watermelons it's less than 10%.
So instead of giving me 4, 2, and 1 it's giving
me 0, 0, and 0.
Now the trick here is that charts when you
have a zero value won't bother to draw a wedge.
So these will be missing if I were to do a
chart of table 2.
But I still need to figure out how to get
that Other in there.
So let's just shrink this table up here but
I'm also going to preserve a row and give
myself a footer there.
Instead of this being total here I'm going
to make this Other.
How am I going to get Other?
Well, I'm going to use the same kind of formula
but a little different.
So I'm going to use the SUMIFS function, that's
IF with an S at the end.
If I look it up over here in the Help, you
can see it's sum values, test values, and
condition.
Which it what I want.
So I want it to add up the values here, so
I'm going to click on B there from Table 1
sales.
I'm going to test the values here, so Table
2 sales column.
The test I'm going to perform is with quotes
around it equals zero, quote, then close the
parentheses.
So basically if the value here is equal to
zero, so it's only true for peaches, pears,
and watermelons, then I'm going to add to
the sum from here.
So the 4, 2, and 1 will be added to the sum.
The result is I get 7.
So I now have these values here, zeros for
these so they shouldn't show up in the pie
chart, and 7 for Other.
Now let's create the pie chart using this
table.
Select it, pie chart, I get it there and sure
enough it doesn't have these three in there
because they are zeros.
But it doesn't have that in there either.
I want to click Edit Data References.
It doesn't include that because it's a footer
row.
I can force it to include it.
Now it's including the footer row there.
Now I can see Other is present.
So now I can do different things with the
chart like let's get rid of the legend because
the legend includes some extra stuff there.
But I can add, under the wedges, I can say
Data point names.
So now I get the names there and I get a nice
chart.
And it updates.
So, for instance, if I were to increase peaches
so that it is above 10%, now you can see peaches
is included.
Only pears and watermelons are included with
Other which are just three there.
Also if I go back to the chart here I can
change the data format to be the number.
So I can change it to number rather than percentage.
So some really cool stuff.
I can, of course, update the formula here.
These formulas, if I change it.
0.1 that's ten percent so that's my threshold
there.
I can include that as a number somewhere else
and refer to it or I can just go and change
the formula and Copy and Paste it in.
That's that one threshold that you can change.
So this is how you get to have a pie chart
with Other in there rather than having tiny
slivers of insignificant data.
