Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero's, Rounding, and Hiding
Hi All,
I'm creating a spreadsheet that has some time calculations in it. The problem is that I want to hide all zero values. Now, I've already got the spreadsheet set to suppress all zeros, and I've made sure with conditional formatting and some formulas include 'zero-trapping' as well. Now, for some calculations, the sheet is returning a value of "0.00" but the REAL value of the calculation is 0.00003333. I've tried rounding the result, to no avail. I've tried setting the format to only have two digits, to no avail. This has been driving me nuts for nearly a week now. Please help!! Thanks, Ashley. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero's, Rounding, and Hiding
Which version of Excel are you using?
In Excel 2003 if I have Tools/ Options/ View set not to display zero values, if I then use =ROUND(A1,2) to round the 0.00003333 to 0.00 it is no longer displayed. If you didn't want to round the numbers you could use Conditional Formatting with "Cell Value is" "between -0.0005 and 0.0005" (or whatever limits you prefer) to set the font to white. Without knowing what you've tried, it's difficult for us to tell you why it didn't work. You need to remember that formatting doesn't change the contents of the cell (unless you've got "precision as displayed"), but only affects how it is displayed, whereas functions such as ROUND will affect the value. -- David Biddulph "Ashley" wrote in message ... Hi All, I'm creating a spreadsheet that has some time calculations in it. The problem is that I want to hide all zero values. Now, I've already got the spreadsheet set to suppress all zeros, and I've made sure with conditional formatting and some formulas include 'zero-trapping' as well. Now, for some calculations, the sheet is returning a value of "0.00" but the REAL value of the calculation is 0.00003333. I've tried rounding the result, to no avail. I've tried setting the format to only have two digits, to no avail. This has been driving me nuts for nearly a week now. Please help!! Thanks, Ashley. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero's, Rounding, and Hiding
If you have Zero Suppression set (Tools.Options.Zero_Values), then any value
that is rounded to 0 would not be visible. Example: =ROUND(0.00003333,3) would resolve to 0 and would not be visible. What formula are you using to round the results? Note: If you're using a Number Format to round....that only impacts the display, NOT the value. Zero Suppression reads the value. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ashley" wrote in message ... Hi All, I'm creating a spreadsheet that has some time calculations in it. The problem is that I want to hide all zero values. Now, I've already got the spreadsheet set to suppress all zeros, and I've made sure with conditional formatting and some formulas include 'zero-trapping' as well. Now, for some calculations, the sheet is returning a value of "0.00" but the REAL value of the calculation is 0.00003333. I've tried rounding the result, to no avail. I've tried setting the format to only have two digits, to no avail. This has been driving me nuts for nearly a week now. Please help!! Thanks, Ashley. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero's | Charts and Charting in Excel | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
Zero's # 2 | Excel Discussion (Misc queries) | |||
Zero's | Excel Discussion (Misc queries) | |||
Zero's | New Users to Excel |