Chart Data

[D Willett]

Hi Guys

I'm creating a chart based on sales and a target. I have the chart working but it needs a tweek to not show sales if they are zero or "".

Here is an example of the formula used:

=IF(G4="","",G4+H3)

ScreenHunter_015.jpg

ScreenHunter_017.jpg

The chart still shows £0 as you can see from the below chart output:

ScreenHunter_016.jpg

The label for the blue sales line uses the following Custom format:

\£#,##0

How can I change this to not show the descending blue sales line if the cell is Blank or ""

Many Thanks

[HansV]

Make the formulas return NA() if the input is empty:

=IF(E3="", NA(), E3+F3)

etc. If the chart still dips to zero, click in the chart and click 'Select Data' on the 'Chart Design' tab of the ribbon.
Click 'Hidden and Empty Cells'.
Make sure it looks like this:

S2457.png

[D Willett]

Spot on Hans.

I was applying the NA() to the 'Incremented' column and reviewed the settings in the 'Hidden and Empty Cell Settings' dialog which made no difference, didn't think to try the 'Combined' column first.

Thank you again Buddy