Ignoring a blank cell (not interpreting it as a zero)

S

SemiMBA

New Member
Joined
Dec 18, 2007
Messages
1
I am using Excel 2002 and am having an issue with a spreadsheet. Hopefully some of you can assist me.

I have generated a spreadsheet which allows a user to enter particular values along a row (total of 12 values in 12 cells). 5 days a week, only 6 of these cells are filled out, and 2 days a week, all 12 values are filled out. The cell values are automatically charted on a different page of the spreadsheet which allows me to track certain events over time. This is where I have a problem.

Everything is fine during the two days of the week that the entire 12 cells are filled out. However, when only 6 cells are filled out, it skews the data interpreting no entry as a 0 (zero). Is there anyway that I can enter in an equation or macro which would cause the charted data to ignore data that is not entered? Something like ignore data that is =0 or blank?

Thanks!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Welcome to the board.

In your chart source range formulas, set zero's to show NA() instead of "" or 0.
You will see N/A# in your table but they will not show or plot in the chart or chart table.
Use a formula similar to this…
Code: 
=IF(C35=0,NA(),C35)
 
Last edited:
Upvote 0
Datsmart--I've clicked Label Contains=Value for Format Data Series.

I'm using IF and NA() to get an NA but it shows since I'm showing Values, so is there a Custom Format for Font that would not show anything.

I normally show values on each column and don't use gridlines so the charts look a little cleaner...
 
Upvote 0
Datsmart--thanks. I'm trying a few other things also...
 
Upvote 0
Seem to have it with this:

Format Data Labels, Number, Custom Format and select 0;-0;;@
 
Upvote 0
Can't get that to eliminate the "#NA"'s Data Labels on my Excel version 2000.

If it works for you, well done.
 
Upvote 0
You're correct if I use:

=IF(Stats!B20<1,NA(),Stats!B20) the NA# shows even if custom format is 0;-0;;@

But if I use =IF(Stats!B20<1,"",Stats!B20) or just plain =Stat!B20 and use custom format 0;-0;;@ the value of the cell is not shown for the Data Series.
 
Upvote 0
OK, I think I miss interpreted your initial request.
NA() is normally used on a line chart where you do not want the chart line to dip to the "zero" positions when there is no data.

Glad you got it worked out on your own.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Clear Info after 7 days
Replies
1
Views
114
Micron
M
P
  • Question
OR function that ignores blanks
Replies
1
Views
446
KRice
K
N
  • Question
Graph X-Axis Remove Zero Values
Replies
2
Views
245
nature98
N
J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
496
Jaxi
J
R
  • Solved
Ignoring the value of a formula in a cell.
Replies
8
Views
309
Royalbloodi
R

Forum statistics

Threads
1,215,338
Messages
6,124,358
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top