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)
The chart still shows £0 as you can see from the below chart output:
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
Chart Data
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Chart Data
You do not have the required permissions to view the files attached to this post.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Chart Data
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:
=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:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Chart Data
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
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
Cheers ...
Dave.
Dave.