Preventing "" (Null set) as showing as 0 on a chart

Try =IF(A1="",#N/A,Average(A1:A10))

I'm having the same problem. I tried to change my "" to #N/A, as suggested, however, my chart still shows a zero value. Any idea why? TIA!

Are you sure that your IF condition is met? In
this case A1="". Is it possible that the 0 is
the result of the 3rd IF() argument?

The #N/A is showing in the cell, so yes, the IF condition is being met. My chart, however, still reflects 0.

In the Tools, Options menu I have "Not plotted (leave gaps) for the "Plot empty cells as:" choice.

Thanks again!

Okay, I can see now that this'll drive me nuts! : )
What's your chart type? Can you paste the problematic
SERIES() function into a followup posting? Can
you also include a data sample that replicates
your problem?

Looking at...

Re: Preventing

It's been driving me nuts for weeks.... Just found this board today and was so relieved!!

I'm not sure this is what you're looking for, so let me know if you need more.

Chart type is line.
='Chart Info'!$E$35:$E$45

--C-----------D--------------E-----
35: 1057____30638.67____28.99
36: 1203____33082.37____27.50
37: 1480____43874.5_____29.64
38: 0_______0___________#N/A

Here is value in E38: =IF(D38=0,#N/A,D38/C38)

Point #4 shows as zero.

Thanks again.... sorry to be driving you crazy!

So...

If you click on your line you see...

=SERIES(,,'Chart Info'!$E$35:$E$45,1)

...in the formula bar? So far, I haven't
been able to reproduce your problem

Re: Preventing

Hi Michelle

I may be wrong here but, you don't have the #N/A enclosed in quotations do you ?

I have found the most consistent way of not plotting particular cells in to simply hide the rows or columns. Excel will not plot hidden Rows or Columns.

If you prefer, this can be done automatically. To do this you should use this code, placed in the Worksheet Module:

Private Sub Worksheet_Calculate()
'Wriiten by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''
'Hides Rows in that have and Error value in Column A

On Error Resume Next
Columns(1).SpecialCells _
(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True

Columns(1).SpecialCells _
(xlCellTypeFormulas, xlNumbers).EntireRow.Hidden = False

End Sub

For this to work have your formulas return #N/A when they are False.
Then right click on the Sheet name tab and select "View Code" and simply paste in the above code.
Push Alt+Q to return to Excel and Save.

Now each time you Worsheet recalculates any rows in column A that are returning an error value will be hidden. As soon as they return True, and display a number they will be unhidden.

I have a few other small Tips for Charts on my Website under "Chart Tips and Tricks" that may be of interest to you.

Dave

OzGrid Business Applications

Re: So...

I see:

=SERIES("Total",,'Chart Info'!$E$35:$E$45,3) If you click on your line you see... =SERIES(,,'Chart Info'!$E$35:$E$45,1) ...in the formula bar? So far, I haven't

Re: Preventing

Dave--

Thanks for the suggestions and the website. I would like to hide the rows automatically. I tried using your code, but it's not working. A couple of questions:

When I first go to "view code", this is shown:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

Do I delete that out?

Next question... The #N/A is in column E. Does the code need to be adjusted for that?

Last question... Should I have quotes around the #N/A?

Thanks,

Michelle Hi Michelle I may be wrong here but, you don't have the #N/A enclosed in quotations do you ? I have found the most consistent way of not plotting particular cells in to simply hide the rows or columns. Excel will not plot hidden Rows or Columns. If you prefer, this can be done automatically. To do this you should use this code, placed in the Worksheet Module:

I tried your suggestion and it works great

IF(A1="",#N/A,Average(A1:A10))

But I was disappointed with all of the #N/A's being displayed where I really wanted empty cells. Then I thought Duh!, Conditional Formatting. Now I use the formula above and then the following conditional format and the #N/A's disappear:

Condition 1 - Formula is - =$A$1=""
Format= White Font on White or No Color Background

Thanks for your help!

Re: Preventing

Thanks for the suggestions and the website. I would like to hide the rows automatically. I tried using your code, but it's not working. A couple of questions: When I first go to "view code", this is shown: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub Do I delete that out? Next question... The #N/A is in column E. Does the code need to be adjusted for that? Last question... Should I have quotes around the #N/A? Thanks, Michelle : Hi Michelle : I may be wrong here but, you don't have the #N/A enclosed in quotations do you ? : I have found the most consistent way of not plotting particular cells in to simply hide the rows or columns. Excel will not plot hidden Rows or Columns. : If you prefer, this can be done automatically. To do this you should use this code, placed in the Worksheet Module

Hi Michelle

>Do I delete that out?
Yes, or just paste over the top.

>The #N/A is in column E. Does the code need to be adjusted for that?
Yes, I have made the adjutment in the code below:

>Should I have quotes around the #N/A?
No, that would stop it from working.

Private Sub Worksheet_Calculate()
'Wriiten by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''
'Hides Rows in that have and Error value in Column A

On Error Resume Next
Columns(5).SpecialCells _
(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True

Columns(5).SpecialCells _
(xlCellTypeFormulas, xlNumbers).EntireRow.Hidden = False

End Sub

Any more problems, let me know.

OzGrid Business Applications

Re: Preventing

Dave --

Thanks very much for all the help. I got this to work with the modified code. Greatly appreciated!

~Michelle