Archive of Mr Excel Message Board

Check out Bill's new book on Charts and Graphs for Microsoft Office Excel 2007

Back to Charting for Excel archive index
Back to archive home

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

Posted by DJ Woodward on May 03, 2001 10:14 AM
I sometimes use formulas that result in a "" for example IF(A1="","",Average(A1:A10)). This will leave the cell blank but if you chart the cell with the formula it displays a zero. Does anyone know how to prevent this? Thanks in advance for your help

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

Posted by Mark W. on May 03, 2001 11:17 AM
Try =IF(A1="",#N/A,Average(A1:A10))


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

Posted by Michelle on May 03, 2001 12:56 PM
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!


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

Posted by Mark W. on May 03, 2001 1:14 PM
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?


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

Posted by Michelle on May 03, 2001 1:37 PM
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!


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

Posted by Mark W. on May 03, 2001 1:43 PM
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?


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

Posted by Michelle on May 03, 2001 2:11 PM
Looking at...


Re: Preventing

Posted by Michelle on May 03, 2001 2:15 PM
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...

Posted by Mark W. on May 03, 2001 2:43 PM
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

Posted by Dave Hawley on May 04, 2001 3:31 AM
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...

Posted by Michelle on May 04, 2001 6:33 AM
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

Posted by Michelle on May 04, 2001 7:34 AM
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:



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

Posted by DJWoodward on May 04, 2001 1:10 PM
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

Posted by Dave Hawley on May 04, 2001 2:32 PM
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

Posted by Michelle on May 08, 2001 5:34 AM
Dave --

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

~Michelle



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.