Excel: Empty cell in 2007

[Closed]
Report
-
 Sushant -
Hello,
When presenting raw of data into a chart there is an option to present empty gaps as zero, gaps or connect the dots. none of teh option is working for me.
If i would like to use this forume: =if((sum(a1:a5))=0,"",(sum(a1:a5)))
whats should i put instead of the "" as it does not defined the cell as empty

4 replies

Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
You just use NA() function and excel chart will treat it as empty cell ..... then you can hide it in printing by File > Page Setup > Sheet > Cell Error <Blank> ...

You can hide Error values by conditional formating by putting this formula =+ISNA(C5) suppose values are in C5 ......

So your formula is

=if((sum(a1:a5))=0,NA(),(sum(a1:a5)))

5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Y E S ! ! !

Brilliant ! I've been struggling with this since yesterday, awesome stuff.

(I used the #N/A method - it still seemed to count " " as Zero)


Ta - Nathan.
This does not work in Excel 2007. It labels the bars with #N/A. I tried NA(), " ", and "" but the space and empty string both show zeros. The only thing I found that worked was to delete the formula entirely out of any cell with a zero result.
****** ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! *************
Found a better solution! Leave your formulas and let them return the zeros.
Then in your chart, add data labels, then format the labels to hide the zeros. Got to Edit Labels, Number options, and use a Custom format and enter 0;;; then click Add. You might have to change the 0 to 0.0 or 0% or whatever is appropriate for your data values.
The format string syntax is:
positive value format; negative value format; zero value format; text value format.
Gr8 work You made my day!! thx.
Posts
1
Registration date
Friday April 17, 2009
Status
Member
Last seen
April 17, 2009
2
Did you find an answer?
I have the same problem.
Inan IF-function, I do not know which empty result would make an empty cell.
I had the same problem but the 1st person to post figured out the answer accidently,

=if((sum(a1:a5))=0,"",(sum(a1:a5))) is WRONG

=if((sum(a1:a5))=0," ",(sum(a1:a5))) is RIGHT

i put in the exact formula as him but put " " instead of "" and it works
if you put #N/A instead of "" excel won't show anything in your chart. Looks ugly in the source data, but pretty in the chart!