ExcelBanter - How to make Excel Chart not to display empty cells?

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