Make a pie chart go from one value to four by changing its data set

[rizzo93]

I'm trying to make a pie chart show one segment under one condition and then four segments under another condition. So basically, I want the set of data to change from just one value to four values.

I tried using OFFSET to define a range that would change its dimensions, but Excel doesn't like me putting in the name of the defined range in the Select Data window.

First condition:
When x=FALSE, show one value at 100% and have its own color.

Second condition:
When x=TRUE, show four values at whatever percentage they are with their corresponding colors.

In essence, I want the pie chart to look as though it's not active (nothing to report) until the condition is true at which time it will show the colored segments. So it would look like a light shade of grey until it comes alive.

Is there a way to do this using formulas?

 

[RoryA]

You can assign a defined name as the data source for a chart, but you should assign it to the series, not to the chart as a whole, and you need to prefix it with either the sheet or workbook name.

 

[rizzo93]

Adding the worksheet name definitely helped, but I'm still not getting the desired result.

This is my revised formula:

=IF(filters.applied,OFFSET(Calculations!$G$14,,,4,1),OFFSET(Calculations!$G$18,,,1,1))

filters.applied is either TRUE or FALSE. I should also add that when this variable is TRUE, it means I'm making one or more selections from a slicer, so the numbers will change (stay with me; I'll explain this below).

If TRUE, then it should take four rows worth of one column; FALSE, then move down below that data (same column) to take just the one value I want when the pie chart is "not active."

Right now, all I'm getting is a 100% pie chart no matter how many slicer choices I add to the filtering.

 

[RoryA]

It's going to be pretty hard for me to troubleshoot without seeing the chart. Can you put it on a site like OneDrive or Dropbox so I can have a look?

 

[rizzo93]

My apologies. The table below is the one being used to generate the chart.

As you advised, I have defined the range using the second column only.

I want the last value (100) to be the "non-active" stage of the chart and the first 4 rows (16 to 7) when the chart is "active".

I appreciate your help.

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]Challenged
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]At Risk
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]On Track
[/TD]
[TD]68
[/TD]
[/TR]
[TR]
[TD]On Hold
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Blank
[/TD]
[TD]100
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

It works for me. Where is that table located? Does it start in F14?

 

[rizzo93]

Yes, the table is located on a sheet called Calculations:

OFFSET(Calculations!$G$14,,,4,1)

 

[RoryA]

And the table starts in F14, not G14?

As I said, it works fine for me.

 

[rizzo93]

Weird. The offset "anchor" is G14. Thanks for verifying that it works.

I had a chart giving me trouble yesterday. When I started over, it started to behave. Maybe I need to do that for this one as well. I'll keep trying.

Again, thanks so much for your help. Very much appreciated!

 

[rizzo93]

And the table starts in F14, not G14?

As I said, it works fine for me.

I reread what you said and I want to clarify. F14 is where the first word, "Challenged", is but my defined name range starts in G14. Does that make a difference?