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



"Marko Pinteric" wrote in message
...

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



"Marko Pinteric" wrote in message
...

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



"Marko Pinteric" wrote in message
...

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




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" wrote in message
...
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




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" wrote in message
...

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