How to make Excel Chart not to display empty cells?

M

Marko Pinteric

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
 
P

Peter Rooney

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
 
M

Marko Pinteric

Kelly said:
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

[email protected]
Click to expand...


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
 
K

Kelly O'Day

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

[email protected]


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
 
M

Marko Pinteric

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.
 
Top