Still need help please

  • Thread starter Thread starter Ilana
  • Start date Start date
I

Ilana

I posted yesterday that I have created an IF statement that either
returns a value or a blank (""). The idea is that there is a table of
cumulative numbers that I only want to populate if the month has
occurred and a chart that draws from that worksheet. Problem is that
the chart is reading the blank as a zero. Another user of the forum
suggested that I replace the blank with NA() to get it to return an
error message which the chart will ignore and then conditionally format
the error message to a white font so that it's not visable in the
table. Total genius. Problem is that I can't get the conditional
formatting to work for me. Can anyone help me here? Please remember
that I'm an intermediate user of Excel, so go slowly. Thanks.
 
Ilana said:
I posted yesterday that I have created an IF statement that either
returns a value or a blank (""). The idea is that there is a table of
cumulative numbers that I only want to populate if the month has
occurred and a chart that draws from that worksheet. Problem is that
the chart is reading the blank as a zero. Another user of the forum
suggested that I replace the blank with NA() to get it to return an
error message which the chart will ignore and then conditionally format
the error message to a white font so that it's not visable in the
table. Total genius. Problem is that I can't get the conditional
formatting to work for me. Can anyone help me here? Please remember
that I'm an intermediate user of Excel, so go slowly. Thanks.
Click to expand...

Format > Conditional formatting
Change first box to "FormulaIs".
In second box put the formula
=ISNA(A1)
where A1 should be replaced by the reference of the cell you are formatting
(or the active cell if you are formatting a range all at once).
Then select your white font format, etc.
 
I've done what you suggested, but the #NA is still showing up in th
chart. Can't figure out what I'm doing wrong. I do appreciate th
help though
 
Ilana,
You can force an Excel chart to ignore blank cells...
Tools | Options | Chart (tab) | check the option you want.

Regards,
Jim Cone
San Francisco, Ca
************************************
 
Thanks, but this only controls empty cells. Since there's a reference
in the cell (to the result of the IF statement), the chart is not
reading it as blank.
 
Ilana
Is the #N/A value showing up in your chart, or do you mean table

If you would like to send your file to me I will see if I can apply the solutions I gave you yesterday

Also, Jim's suggestion won't work because your cells aren't actually blank, they contain formulas

Regards
Mark Graesse
(e-mail address removed)

----- Ilana wrote: ----

I've done what you suggested, but the #NA is still showing up in th
chart. Can't figure out what I'm doing wrong. I do appreciate th
help though
 
Yes, I meant the table. I wish I could send you the workbook, but i
contains business info that I'm guessing they wouldn't want shared.
The worst part of the this whole thing is that I'm not an admin....
got drafted to do this b/c the admin. who was asked said it couldn't b
done
 
Ilana said:
I've done what you suggested, but the #NA is still showing up in the
chart. Can't figure out what I'm doing wrong. I do appreciate the
help though.
Click to expand...

You aren't just inserting some text such as "#NA" or "#N/A" or something
similar, are you? You must insert the "NA worksheet function" NA(), for
example
=NA()
or
=IF(A1=1,NA(),"")
You will then see #N/A in the cell, which may look exactly like the text
"#N/A", but the two are very different!
 
Ilana,

Ok, here is a couple more...

Tools | Options | View (tab) | uncheck "zero values"
(no zero values will appear on worksheet)
or
Format the cells with something like this...
General;-#,###;[White]General
(all zero values are formatted in white color)

Regards,
Jim Cone
San Francisco, CA
 
Okay, still trying. Yes, I am inserting NA() which yields the result o
#NA, but I certainly can understand why you would ask me that! :-)
Changing the cells to not show zeros is not helping, as the chart i
still reading them whether they show up or not. What did I get mysel
into here
 
Ilana,
Does this mean that the data you are charting is not directly in the table but is referenced from the table. If so, you need to use the IF statement with the NA() in the referencing area.

Since you can't send me your file, I could send you some simple examples I have put together while testing your problem. If you would like the examples, e-mail me at (e-mail address removed) and I will reply with the file.


Regards,
Mark Graesser
(e-mail address removed)


----- Ilana wrote: -----

Thanks, but this only controls empty cells. Since there's a reference
in the cell (to the result of the IF statement), the chart is not
reading it as blank.


---
Message posted



----- Ilana wrote: -----

Yes, I meant the table. I wish I could send you the workbook, but it
contains business info that I'm guessing they wouldn't want shared.
The worst part of the this whole thing is that I'm not an admin....I
got drafted to do this b/c the admin. who was asked said it couldn't be
done.
 
We resolved this offline. The final solution used an IF statement with the NA() function for the charting, and a white font conditional formatting for the Table

Regards
Mark Graesser
 
Back
Top