Converting 0 values to Blanks
 

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.

Converting 0 values to Blanks
 

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

--
Gary''s Student - gsnu200908


"kepetersen3405" wrote:

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.

Converting 0 values to Blanks
 

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.

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200908

Converting 0 values to Blanks
 

xl-2007

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


"kepetersen3405" wrote:

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.

Converting 0 values to Blanks
 

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.

"Teethless mama" wrote:

xl-2007

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


"kepetersen3405" wrote:

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.

Converting 0 values to Blanks
 

On Nov 14, 2:21*am, kepetersen3405
wrote:
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.



"Teethless mama" wrote:
xl-2007

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

"kepetersen3405" wrote:

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.- Hide quoted text -

- 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)