"Extra-conditional" formatting of bar chart

[red_leader]

Hi!

I have a bar chart that I have formatted to automatically display the individual bars in different colors depending on their value. However, my boss now wants me to omit any data points (bars) that have a zero value from the chart. I can't figure out how to do this automatically and preserve the other automatic formatting at the same time. The file will be used by several different users who will all have their own data categories (in column V). Can anyone help me? I can't resort to macros to solve this, either. File attached -

 

[Hui]

You will need to use a few Named Formula


The Named Formula are all prefixed cht
chtRisk
cht75
cht60
cht45 &
cht50

These all use a LastRow named Formula to determine the last row which doesn't have an error

Then change the chart to refer to the Named Formula above

Enjoy

 

[red_leader]

You will need to use a few Named Formula
View attachment 42387

The Named Formula are all prefixed cht
chtRisk
cht75
cht60
cht45 &
cht50

These all use a LastRow named Formula to determine the last row which doesn't have an error

Then change the chart to refer to the Named Formula above

Enjoy

Thanks Hui! I am going through this trying to figure out exactly what you did on the file. If it's OK, I may ask you a follow up question later. In the meantime, I sent you a thank you

 

[red_leader]

Hi Hui -

So (I think) I made all the changes per your instructions and example that you sent back to me, but the chart isn't working correctly. What I want the chart to do is only graph the risk entries on the list that have a value, but with the changes the graph is now showing some entries that don't have a value and not all of the ones that do have a value. Can you help me get this straightened out? Not sure what I did wrong... I've attached the updated version below - thanks!

 

[red_leader]

Just to tack on to the above, I realized that the graph is correctly omitting entries with 0 values (one has a negative value, which confused me initially but that's another issue). However, it is NOT showing all of the entries that do have values. Can you help correct this? FYI, there are several end users of this document, each of which will have values for some (but not all) of the entries on the list. The entries with values will vary by user, so the formula needs to automatically recognize the ones with values and chart them, but not the others. Hope that makes sense. Thanks again for your help -

 

[Hui]

I have used some helper cells to extract the data
then used the named formulas on the helper cells

To understand how the helper cells work refer:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/