Need help please

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

Ilana

Let me start from the beginning. I am trying to create a cumulativ
value that would show only when that month is included (i.e.
cumulative number would show up in Nov. once a monthly number i
entered in Nov.). That value, when present, is then linked to a char
on another worksheet. To do this, I have created a formula that look
at a cell and either returns a value or "". By "", I wanted t
return a null value. So, what's in the cell that the chart is lookin
at is a reference to the formula result which I thought was a nul
value, but the chart is reading it as a zero. Any advice
 
Ilana,
You could replace your "" with NA(). It won't look so good on the table but it will suppress the data point on the chart.

Good Luck,
Mark Graesser
(e-mail address removed)

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

Let me start from the beginning. I am trying to create a cumulative
value that would show only when that month is included (i.e. a
cumulative number would show up in Nov. once a monthly number is
entered in Nov.). That value, when present, is then linked to a chart
on another worksheet. To do this, I have created a formula that looks
at a cell and either returns a value or "". By "", I wanted to
return a null value. So, what's in the cell that the chart is looking
at is a reference to the formula result which I thought was a null
value, but the chart is reading it as a zero. Any advice?
 
I tried to do as you suggeted to see what it would look like, and I go
an error in the formula. Thanks, but I still need help
 
I think I misunderstood your suggestion. Regardless, the cell needs t
appear to be blank in the table and not plot on the graph. I don'
think that this solution would yield that result. Thanks for tryin
though
 
In your original post you stated you "have created a formula that looks
at a cell and either returns a value or "". I assume this is an IF statement. Instead of having the IF statement return a "" you can have it return a #N/A error by replacing the "" in your IF statement with NA(). When charting data Excel won't ignore cells that have fake blanks (""), but it will ignore cells that have a #N/A error.

You could hide the errors on your table with conditional formatting. The contion would be:

formula is =NA(A1) replace A1 with the active cells cell reference.

Then change the conditional font color to white. This way the error will prevent the point from being plotted on the chart, and the white font will prevent the error from being visible in the table.

Hope this helps,
Mark Graesser
(e-mail address removed)

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

I think I misunderstood your suggestion. Regardless, the cell needs to
appear to be blank in the table and not plot on the graph. I don't
think that this solution would yield that result. Thanks for trying
though.
 
One more thing. Post your original formula and the one which gives you an error. Maybe I can tweak it for you.

Regards,
Mark Graesser
(e-mail address removed)
 
Back
Top