Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart values for blanks show as zero (Excel 2003) | Charts and Charting in Excel | |||
converting hourly values into daily values | Excel Worksheet Functions | |||
Paste Special as Values: no recognition of blanks | Excel Discussion (Misc queries) | |||
Replace values in shaded cells with blanks | Excel Discussion (Misc queries) | |||
If Then, not using values, or not counting blanks | Excel Worksheet Functions |