Modification of Charting Function

**meppwc** · 02-05-2016, 11:53 AM

I have a chart that displays the values found in cells A1:A5.
The values for these cells are numerical and formatted as percentage.
Currently,A1 thru A4 have numerical values (10%, 35%, etc) and A5 has no value for this month(the cell is blank)

I have added a chart table that individually maps each cell and the value in cells A1 thru A4 show up in the Chart Table as they should.
But in the Chart Table cell A5 shows up as 0% even though though cell A5 is blank

Is there a way make the chart display nothing opposed to the 0% it is currently displaying?

**MrShorty** · 02-05-2016, 12:31 PM

I think your description of the problem is slightly incorrect. If cell A5 is truly blank (so that =ISBLANK(A5) would return TRUE, meaning there is absolutely nothing in that cell), then it should not include the 0%. This usually happens with "blank" cells that are not truly blank, but contain functions that usually return empty string ("") or space (" ") or other text string that makes the cell look empty.

Charts treat text strings as the value 0. When you want to "hide" an entry from the chart, you are better off using the NA() function instead of empty string. I suggest this tutorial: http://peltiertech.com/mind-the-gap-...g-empty-cells/

**meppwc** · 02-05-2016, 12:44 PM

MrShorty........you are correct..........A5 contains the formula =AR18. And AR18 contains the formula =IF(AP18+AQ18=0,"",AP18/(AP18+AQ18))

It is the result that you can see in the cell that is blank
So based on this, is there a workaround I an use to achieve what I want?

**MrShorty** · 02-05-2016, 01:13 PM

As explained in my post and Jon's essay, use the NA() function instead of empty string "".

**meppwc** · 02-05-2016, 01:27 PM

worked great, thanks so much

Modification of Charting Function

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Modification of Charting Function

Re: Modification of Charting Function

Re: Modification of Charting Function

Re: Modification of Charting Function

Re: Modification of Charting Function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Concatenate Function Modification

[SOLVED] Modification of the function CountIfs VBA

FileName / Path Function Modification

[SOLVED] I want a little bit of modification to normal VLOOKUP function.

Offset Function To Skip Blanks/Zeros and Corresponding Header When Charting

VBA error in charting function. Help!

Custom charting - Stacked charting with a line

Bookmarks

Bookmarks

Posting Permissions