Charts & Data Labels Using =NA()

[CWatts]

Short version: is there a way to show data labels for everything with data, but not label =NA() values?

Long version & background:
There is a small table based on VLOOKUPs which populate the data for the chart. The data is currency ranging from -$10 to $30. These are vlookups coupled with IFERROR statements that display an NA() if there is no data. The NA is crucial as text is charted at 0 and I need the chart to not display anything for weeks without data.

Now the issue that comes up is that I want the #N/A's to be hidden or displayed as "" in the data labels, but i'm stuck.

The only semi related post I could find is as follows, but it doesn't work with Excel 2007:

http://www.mrexcel.com/forum/showthread.php?t=77006&highlight=#N/A+Data+label

My data label number is currently formatted as such:

$0.00;[Red]-$0.00;$0;""

 

[Jon Peltier]

NA() suppresses the plotting of points in line and XY charts, but not in bar charts. Use "" instead, and apply the number format that suppresses zero values.

 

[CWatts]

Thanks for the reply, but that's part of the problem I'm having. I can set the data lables to the custom number format and have them show up as blank, and if the number was actually zero I could use $0.0000001 so it rounds to 0 but isn't so it will display.

THe problem with the "" is that text also plots at zero. It's normally not a problem, but since my data ranges from -$10, to $30, that means that zero's show up as if there is data, when in actuallity there may not.

I'm hoping there's a cleaner way to make this work as I wanted to avoid making hidden series.

 

[Jon Peltier]

Is it in fact a bar chart? Or a line chart? Since they behave differently in this situation, it would help to know. For a bar chart, "" is invariably the best choice, whereas for a line chart, NA() is better, since you don't even get a point where the NA() occurs. Given this, I can't tell from your discussion which chart type you have, and why it's causing a problem.

Another option would be to have two columns of data, one using NA() for the formula result, the other using "". Plot the NA(), and use something like Rob Bovey's Chart Labeler to use the second column for labels.

 

[CWatts]

Ugh.... I thought I had discussed it but it seems I deleted that paragraph describing the charts.

It is indeed a bar chart (column actually) and now that I have been able to get away from work and revisit I think I can more clearly articulate it.

The "" wasn't working because I had set my axis to cross at the bottom of the chart, rather than at the 0 point which is why it kept showing a bar.

To accomplish what I needed I set up the normal series to chart NAs and then set up an additional series which I set to overlap and be transparent with the data labels.

Thanks for taking a look and sorry I missed the chart type.

 

[msaadkamal]

I am using bar charts.
I have made two data series for the same thing just to change color for negative and positve bars. I used if(a1>0,a1,"") for positive numbers and if(a1<0,a1,""). and then put the bars at 100% overlap. It worked fine.

BUT !!

When i insert the data labels. it shows 2 data labels for every same. I tried to work it out but can't and want your help.

What i want is that, it should show only labels for the numbers which are non zero, or which are from the series.

Please help me.

 

[CWatts]

Assuming you have your table in a simple setup like A1:G2, then you can change your source to get the chart you want based on what we have below.

When you first chart it, you will have three bars on your chart. Look at your legend to see which corresponds with which. Select the series that has your data labels and set them to show.

Format your data labels as follows: #;[Red]#;"";""

On the Data Label data series, adjust it so that fill is "None" and line color is "None" and set overlap to 100% THen you can hide your legend, or remove some of the entries from the legend by clicking once to select the legend, clicking once more on the NAME of the series you want to remove and then pushing delete.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD style="TEXT-ALIGN: center">1-Jan</TD><TD style="TEXT-ALIGN: center">2-Jan</TD><TD style="TEXT-ALIGN: center">3-Jan</TD><TD style="TEXT-ALIGN: center">4-Jan</TD><TD style="TEXT-ALIGN: center">5-Jan</TD><TD style="TEXT-ALIGN: center">6-Jan</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Data</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">-5</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">-4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD style="TEXT-ALIGN: center">1-Jan</TD><TD style="TEXT-ALIGN: center">2-Jan</TD><TD style="TEXT-ALIGN: center">3-Jan</TD><TD style="TEXT-ALIGN: center">4-Jan</TD><TD style="TEXT-ALIGN: center">5-Jan</TD><TD style="TEXT-ALIGN: center">6-Jan</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Data Pos</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Data Neg</TD><TD style="TEXT-ALIGN: center">-3</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">-5</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">-4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Data Label</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">-5</TD><TD style="TEXT-ALIGN: center">-3</TD><TD style="TEXT-ALIGN: center">-4</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B5</TD><TD>=IF(B2>0,B2,"")</TD></TR><TR><TD>C5</TD><TD>=IF(C2>0,C2,"")</TD></TR><TR><TD>D5</TD><TD>=IF(D2>0,D2,"")</TD></TR><TR><TD>E5</TD><TD>=IF(E2>0,E2,"")</TD></TR><TR><TD>F5</TD><TD>=IF(F2>0,F2,"")</TD></TR><TR><TD>G5</TD><TD>=IF(G2>0,G2,"")</TD></TR><TR><TD>B6</TD><TD>=IF(B2<0,B2,"")</TD></TR><TR><TD>C6</TD><TD>=IF(C2<0,C2,"")</TD></TR><TR><TD>D6</TD><TD>=IF(D2<0,D2,"")</TD></TR><TR><TD>E6</TD><TD>=IF(E2<0,E2,"")</TD></TR><TR><TD>F6</TD><TD>=IF(F2<0,F2,"")</TD></TR><TR><TD>G6</TD><TD>=IF(G2<0,G2,"")</TD></TR><TR><TD>B7</TD><TD>=B2</TD></TR><TR><TD>C7</TD><TD>=C2</TD></TR><TR><TD>D7</TD><TD>=D2</TD></TR><TR><TD>E7</TD><TD>=E2</TD></TR><TR><TD>F7</TD><TD>=F2</TD></TR><TR><TD>G7</TD><TD>=G2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Jon Peltier]

You need to use custom number formats for your data labels, like

0;;; to show positive values in the positive series' labels
;-0;; to show negative values in the negative series' labels

Or you could use Invert if Negative formatting using only one series.

 

[msaadkamal]

Thank you so much CWats and Jon Peltier, I must accept you guys are the gurus.

I am able to do it. I have some further queries


I have data like 6.2% -3.1% 2.5% -1.2%

What custom format should i use if I want the numbers to show in percentage with one digit after decimal with green color for positive numbers and red color for negative numbers. Also if i want to put the positive and minus sign also, thanks in advance

 

[CWatts]

[Green]+0.0%;[Red]-0.0%;0%;""