How to make Excel Chart not to display empty cells?

I have a range of IF formulas. Sometime IF returns number, sometimes
"". The problem is that chart displays "" as value 0. Is there a way
to force chart not to display those empty values?

IF(condition; number_value; "")

Marko

Marko:

In your If statement, replace "" with Na(). Excel will place a #N/A in the
cells that were getting the "". Excel charting recognizes #N/A and will not
go to 0.


See this post for more details.
http://processtrends.com/pg_charts_missing_data.htm

...Kelly

koday@processtrends.com

"Marko Pinteric" <marko@pinteric.com> wrote in message
news:XQOYf.1486$oj5.585016@news.siol.net...
>
> I have a range of IF formulas. Sometime IF returns number, sometimes "".
> The problem is that chart displays "" as value 0. Is there a way
> to force chart not to display those empty values?
>
> IF(condition; number_value; "")
>
> Marko

Marko,

if you hide the row or column (depending on which way your data is oriented)
that contains the x-axis label and the "" value, then this will be omitted
from your chart.
If you know VBA, you could write a macro that scanned each cell in your plot
range and hid its row/column if the cell value equalled "", and a
corresponding macro to unhide all rows afterwards.

Hope this helps

pete


"Marko Pinteric" wrote:

>
> I have a range of IF formulas. Sometime IF returns number, sometimes
> "". The problem is that chart displays "" as value 0. Is there a way
> to force chart not to display those empty values?
>
> IF(condition; number_value; "")
>
> Marko
>

Kelly O'Day wrote:
> Marko:
>
> In your If statement, replace "" with Na(). Excel will place a #N/A in the
> cells that were getting the "". Excel charting recognizes #N/A and will not
> go to 0.
>
>
> See this post for more details.
> http://processtrends.com/pg_charts_missing_data.htm
>
> ..Kelly
>
> koday@processtrends.com
>
> "Marko Pinteric" <marko@pinteric.com> wrote in message
> news:XQOYf.1486$oj5.585016@news.siol.net...
>
>>I have a range of IF formulas. Sometime IF returns number, sometimes "".
>>The problem is that chart displays "" as value 0. Is there a way
>>to force chart not to display those empty values?
>>
>>IF(condition; number_value; "")
>>
>>Marko
>
>
>


That works, but only partially.

I have such case

<number1>
<number2>
<number3>
#N/A
#N/A
#N/A
<number4>
<number5>

and then <number3> and <number4> get connected. I want that space
between <number3> and <number4> is empty.

Marko

Hi,

For more info on the subject have a look at.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy
Marko Pinteric wrote:
> Kelly O'Day wrote:
>
>> Marko:
>>
>> In your If statement, replace "" with Na(). Excel will place a #N/A in
>> the cells that were getting the "". Excel charting recognizes #N/A
>> and will not go to 0.
>>
>>
>> See this post for more details.
>> http://processtrends.com/pg_charts_missing_data.htm
>>
>> ..Kelly
>>
>> koday@processtrends.com
>>
>> "Marko Pinteric" <marko@pinteric.com> wrote in message
>> news:XQOYf.1486$oj5.585016@news.siol.net...
>>
>>> I have a range of IF formulas. Sometime IF returns number, sometimes
>>> "". The problem is that chart displays "" as value 0. Is there a way
>>> to force chart not to display those empty values?
>>>
>>> IF(condition; number_value; "")
>>>
>>> Marko
>>
>>
>>
>>
>
>
> That works, but only partially.
>
> I have such case
>
> <number1>
> <number2>
> <number3>
> #N/A
> #N/A
> #N/A
> <number4>
> <number5>
>
> and then <number3> and <number4> get connected. I want that space
> between <number3> and <number4> is empty.
>
> Marko

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Pete and Andy's responses prompted me to try a little VBA.

The procedure below works in two steps:

1. Converts all formulas in user specified column to their values
2. Check eaach cell in user column to see if it is an error (#N/A. etc.)
if error, clears contents.

The result is a dataset with just values. You can then use Excel's plot
empty cells to handle blanks the way you want.

Any thoughts?

...Kelly

koday@processtrends.com


Public Sub Chart_If_Na_conversion()
' ================================================================
' D. Kelly ODay - ProcessTrends.com
'Charting Cells with if Formulas can be a nuisance
' The If Na() work around solves part of problem - eliminates Excel plotting
blanks as zeros
' Excel interpolates values when it sees #N/A - not necessarily what user
wants
' This procedure converts formulas to their values
' Then converts all #N/As to true empty cells
' Ask user for column to convert
'=================================================================
Set st = Application.InputBox("Select column to convert formula to value",
"Convert Formula to Value", Type:=8)
st.Select
cl = ActiveCell.Column
last_row = Cells(Rows.Count, cl).End(xlUp).Row
' Convert all formulas to values
Set temp_rng = Range(Cells(1, cl), Cells(last_row, cl))
temp_rng.Copy
Cells(1, cl).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' Clearcotnents of all error values
For r = 1 To last_row
celltype = ""
If IsError(Cells(r, cl).Value) Then
errval = Cells(r, cl).Value
Select Case errval
Case CVErr(xlErrDiv0): celltype = "Error"
Case CVErr(xlErrNA): celltype = "Error"
Case CVErr(xlErrName): celltype = "Error"
Case CVErr(xlErrNull): cellstype = "Error"
Case CVErr(xlErrNum): celltype = "Error"
Case CVErr(xlErrRef): celltype = "Error"
Case CVErr(xlErrValue): celltype = "Error"
End Select
End If
If celltype = "Error" Then Cells(r, cl).ClearContents
Next r
End Sub




"Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
news:8BC94975-62DD-4D2E-AB67-7E1349F603A2@microsoft.com...
> Marko,
>
> if you hide the row or column (depending on which way your data is
> oriented)
> that contains the x-axis label and the "" value, then this will be omitted
> from your chart.
> If you know VBA, you could write a macro that scanned each cell in your
> plot
> range and hid its row/column if the cell value equalled "", and a
> corresponding macro to unhide all rows afterwards.
>
> Hope this helps
>
> pete
>
>
> "Marko Pinteric" wrote:
>
>>
>> I have a range of IF formulas. Sometime IF returns number, sometimes
>> "". The problem is that chart displays "" as value 0. Is there a way
>> to force chart not to display those empty values?
>>
>> IF(condition; number_value; "")
>>
>> Marko
>>

Thanks for ideas. I am primarly thinking about the solution without
VBA. I will compare all ideas and choose one for my use.

Thanks again.

Marko.

Kelly O'Day wrote:
> Pete and Andy's responses prompted me to try a little VBA.
>
> The procedure below works in two steps:
>
> 1. Converts all formulas in user specified column to their values
> 2. Check eaach cell in user column to see if it is an error (#N/A. etc.)
> if error, clears contents.
>
> The result is a dataset with just values. You can then use Excel's plot
> empty cells to handle blanks the way you want.
>
> Any thoughts?
>
> ..Kelly
>
> koday@processtrends.com
>
>
> Public Sub Chart_If_Na_conversion()
> ' ================================================================
> ' D. Kelly ODay - ProcessTrends.com
> 'Charting Cells with if Formulas can be a nuisance
> ' The If Na() work around solves part of problem - eliminates Excel plotting
> blanks as zeros
> ' Excel interpolates values when it sees #N/A - not necessarily what user
> wants
> ' This procedure converts formulas to their values
> ' Then converts all #N/As to true empty cells
> ' Ask user for column to convert
> '=================================================================
> Set st = Application.InputBox("Select column to convert formula to value",
> "Convert Formula to Value", Type:=8)
> st.Select
> cl = ActiveCell.Column
> last_row = Cells(Rows.Count, cl).End(xlUp).Row
> ' Convert all formulas to values
> Set temp_rng = Range(Cells(1, cl), Cells(last_row, cl))
> temp_rng.Copy
> Cells(1, cl).Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> ' Clearcotnents of all error values
> For r = 1 To last_row
> celltype = ""
> If IsError(Cells(r, cl).Value) Then
> errval = Cells(r, cl).Value
> Select Case errval
> Case CVErr(xlErrDiv0): celltype = "Error"
> Case CVErr(xlErrNA): celltype = "Error"
> Case CVErr(xlErrName): celltype = "Error"
> Case CVErr(xlErrNull): cellstype = "Error"
> Case CVErr(xlErrNum): celltype = "Error"
> Case CVErr(xlErrRef): celltype = "Error"
> Case CVErr(xlErrValue): celltype = "Error"
> End Select
> End If
> If celltype = "Error" Then Cells(r, cl).ClearContents
> Next r
> End Sub
>
>
>
>
> "Peter Rooney" <PeterRooney@discussions.microsoft.com> wrote in message
> news:8BC94975-62DD-4D2E-AB67-7E1349F603A2@microsoft.com...
>
>>Marko,
>>
>>if you hide the row or column (depending on which way your data is
>>oriented)
>>that contains the x-axis label and the "" value, then this will be omitted
>>from your chart.
>>If you know VBA, you could write a macro that scanned each cell in your
>>plot
>>range and hid its row/column if the cell value equalled "", and a
>>corresponding macro to unhide all rows afterwards.
>>
>>Hope this helps
>>
>>pete
>>
>>
>>"Marko Pinteric" wrote:
>>
>>
>>>I have a range of IF formulas. Sometime IF returns number, sometimes
>>>"". The problem is that chart displays "" as value 0. Is there a way
>>>to force chart not to display those empty values?
>>>
>>>IF(condition; number_value; "")
>>>
>>>Marko
>>>
>
>
>