Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Aggregating Data in Other Category on Pie Chart
Excel 2007
Basic Question ---------------- Is there a way to make a pie chart ignore "empty" cells? Note that the cells are not truly empty. The display of data in the cells has simply been "blanked" out using this formula: =IF(B19<1000,"",B19) More Information ------------------ I'm putting together a pie chart that will be updated monthly. There are 40 categories of data, and the values for those categories range from 1 ot 65,000. Obviously, this presents a problem in that the categories with low values will be shown as pie slices so thin as to be basically imperceptible. To solve this problem, I'd like to take all the categories where the value is less than 1,000 and sum them up into one category called Other. I would then like to include the Other category on the pie chart along with all the individual categories that have values greater than 1,000. An additional complicating factor is that the number of categories with values greater than 1,000 will vary from month to month. This month, there are 15 categories with values exceeding 1,000, but next month there could be more of those categories, or less. In an attempt to solve these two problems I've done the following: 1) At the bottom of the list of categories I've created an Other category. The value is determined by =SUMIF(B3:B43,"<1000"), which gives me the sum of all the categories having values less than 1,000. 2) I created a calculated column that shows the category names only when the value for the category 1,000. 3) I created a calculated column that shows the value of the category only when the value is 1,000. This gives me two columns that show the category names and values for just those categories where the value is greater than 1,000. I then built my chart using the two calculated columns, hoping that the rows which don't display data would be ignored. Unfortunately, it doesn't seem to work that way. What I ended up with is a chart that shows blank items in the legend. How can I make it so that the chart shows only the categories where the value is greater than 1,000, plus the Other category, and not show blank items in the legend? --Tom |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aggregating data for a chart | Charts and Charting in Excel | |||
Formatting Chart Category Labels | Charts and Charting in Excel | |||
stock chart category axis | Charts and Charting in Excel | |||
Category Labels(X) axis on a chart: | Excel Worksheet Functions | |||
Aggregating data | Excel Discussion (Misc queries) |