Prev: Why is the chart data wrong when I hover over it in Excel 2007
Next: How do I disable automatic chart creation in Excel?
From: Data Monkey on 12 Nov 2009 15:35
Hi there,

I have a long list of "accounts" that have a dollar value next to them. I
would like to get the top accounts by dollar to show up individually in a pie
chart but have a group encompassing the remainder of smaller accounts, so the
total pie value is summation of the entire set of accounts' values. Same
should be able to be done on a stacked bar.

Is there a dynamic way to do this so I don't have to manually create the
"below the line" group?

Thanks,
-data monkey

From: EricG on 12 Nov 2009 17:32
I don't know if there's an automatic way, but here is what I did with a
helper column and one extra row:

Account Value HiLow
Fred 13342 1
Joe 22483 1
Jane 7384 0
Mary 11583 0
Jim 33144 1
Lewis 18734 1
Johnny 22393 1
Vlad 5868 0
Total Others 24835 1

The formula in the "HiLow" column is like =IF($B2<12000,1,0)

For "Total Others", the formula is like =SUMIF(B2:B9,"<=12000")

Create your pie chart, then use autofilter on your data block to filter out
values in the "HiLow" column that are zero (0). I arbitrarily picked a 12000
line to separate the "big" accounts from the others.

HTH,

Eric

"Data Monkey" wrote:

> Hi there,
>
> I have a long list of "accounts" that have a dollar value next to them. I
> would like to get the top accounts by dollar to show up individually in a pie
> chart but have a group encompassing the remainder of smaller accounts, so the
> total pie value is summation of the entire set of accounts' values. Same
> should be able to be done on a stacked bar.
>
> Is there a dynamic way to do this so I don't have to manually create the
> "below the line" group?
>
> Thanks,
> -data monkey
>
> .
>
 | 
Pages: 1
Prev: Why is the chart data wrong when I hover over it in Excel 2007
Next: How do I disable automatic chart creation in Excel?