Previous Topic Next Topic

Excel: Empty cell in 2007

Closed
RT - Mar 22, 2009 at 02:50 AM
 Sushant - Jun 13, 2012 at 08:56 AM
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
Related:

4 responses

Answer 1 / 4
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 166
May 28, 2009 at 10:32 PM
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
Nate
Sep 29, 2009 at 05:57 PM
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.
0
kevin
Mar 22, 2012 at 02:12 PM
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.
0
kevin
Mar 22, 2012 at 02:22 PM
****** ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! *************
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.
0
Sushant
Jun 13, 2012 at 08:56 AM
Gr8 work You made my day!! thx.
0
Answer 2 / 4
PicaPica Posts 1 Registration date Friday April 17, 2009 Status Member Last seen April 17, 2009 2
Apr 17, 2009 at 11:51 PM
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.
2
Answer 3 / 4
Popz
May 3, 2009 at 11:43 PM
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
1
Answer 4 / 4
CF
May 28, 2009 at 01:37 PM
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!
1

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses