Converting 0 values to Blanks

[kepetersen3405]

I am using Microsoft 2003.

Is there a forumla that will force a zero value to be either null or blank?
I am taking a list from Excel and importing it into Business Objects and
creating a chart. If the value is zero, it creates a zero line on my graph.
If the value is blank, it doesn't display.

I've tried doing a forumla that says if the value is 0 return "" but that
doesn't work for what I want to do.

 

[Gary''s Student]

Say we have data in A1 thru B30 like:

3 0
0 0
3 3
3 2
3 3
2 2
2 1
0 1
1 0
3 2
3 2
2 3
3 1
2 2
1 0
2 3
2 3
3 0
3 2
3 0
3 1
3 2
3 2
1 0
3 3
0 0
0 0
0 1
0 1
3 1

enter and run this small macro:


Sub NothingAtAll()
Dim r As Range, rr As Range
Set r = Range("A1:B30")
For Each rr In r
If rr.Value = 0 Then
rr.Value = ""
End If
Next
End Sub

and the result:

3

3 3
3 2
3 3
2 2
2 1
1
1
3 2
3 2
2 3
3 1
2 2
1
2 3
2 3
3
3 2
3
3 1
3 2
3 2
1
3 3


1
1
3 1

 

[kepetersen3405]

Thanks however it wiped out my formula. I need to keep the formula from
month to month. I want to be able to say if sum(a1:a4)=0 then null else
sum(a1:a4) or something like that. If I do if sum(a1:a4)=0 then "" else
sum(a1:a4) it treats it like a zero in business objects.

 

[Teethless mama]

xl-2007

Office Button > Excel Option > Advanced > Untick show a zero in cells that
have zero value

 

[kepetersen3405]

Thanks, but I tried that too. The cell value is still 0 but just not visible
at this point. I need Business Objects to read it as a blank cell, not a 0
value or "" value.

 

[minyeh]

Thanks, but I tried that too.  The cell value is still 0 but just not visible
at this point.  I need Business Objects to read it as a blank cell, nota 0
value or "" value.







- Show quoted text -

as long as there is a formula in ur cell (no matter what the outcome
it display) it'll never be a blank cell.
a feasible way is to use Gary''s Student's macro (ammended so it
creates a new spreadsheet, merely for exporting purpose)