Charts Referencing Zero’s (1)

Topic

New Reply

When I have a chart whith formulas as its source data, I have an if statement to determine if the figure is zero. If the figure is zero I set the true value to “”. The chart plots this a a zero, I want it the chart to skip this plot, how do I do this?

Reply | Quote

Replies

Cindy, replace the “” with #N/A. Your formula should look somthing like:

=IF(B1=0,NA(),B1)

Reply | Quote

Sam, this then displays #N/A, I was not clear enough. I want the cell to be empty, as it is part of a report. Is there a way to have a Null ( empty cell)?

Reply | Quote

That would be having your cake and eating it too! Assuming that you don’t use “” anywhere else you could record a maco to replace all the NA() with “” and vice-versa. If you have “” elsewhere, then replace NA() with ” ” (bunch of spaces in the quotes). HTH –Sam

Reply | Quote

I think that you need to set the value to NA() rather than “”

Peter

Reply | Quote

Could you use conditional formating to hide the font when NA#, White on White?

Peter

Reply | Quote

Genius! But I cannot get it to work! Can you fix the attached worksheet with conditional formatting!

Reply | Quote

not sure how to do it properly so I used reverse logic
Formated the cell to a white font and used Conditional formating to turn if black if greater than -1!!

Of course you would have to pick a number out side of the possible range for you data

Not sure how to send the sheet back to the forum though

Reply | Quote

That works, but I really hate it! However, don’t knock sucess. Thanks, Peter! I have attached the worksheet. To summarize, we used the NA() function, formatted the font color white, and conditionally formatted the font color black when the cell was > -65,000. See attached final product.

Reply | Quote

See attached – you need to use Formula Is =ISNA(C4).

Reply | Quote

Knew that there had to be a proper way of doing it

Peter

Reply | Quote

Much nicer Rory!

Reply | Quote