I have a number of spreadsheets with pre formatted calculations (Example: qty x unit price = total) these are repeated in several rows. Until I enter the qty and the formula is executed, all the rows have a "zero" in the calc' result. How do I get the cell to be blank instead of showing the zero? In Excel there is a preference you can select that will hide the zeros. I cannot find anything like this in Numbers. Is this possible to accomplish?
typically,
If there is a cell used as input (let's say A1) that is used in a formula in another cell (let's say A2)
then you can make A2 remain blank until there is a value in cell A1 like this:
A2=if(A1="", "", <some calculation>)
this is shorthand that indicates that cell A1 contains the formula:
=if(A1="", "", <some calculation>)
typically,
If there is a cell used as input (let's say A1) that is used in a formula in another cell (let's say A2)
then you can make A2 remain blank until there is a value in cell A1 like this:
A2=if(A1="", "", <some calculation>)
this is shorthand that indicates that cell A1 contains the formula:
=if(A1="", "", <some calculation>)
If you don't want to change your formulas and want to "hide" zeros more-or-less Excel style you could try Conditional Highlighting.
Select the cells where you want to hide zeros.
Click 'Conditional Highlighting'
Add a rule and scroll down to Custom Style:
Set opacity to 0 after clicking the color "globe" or whatever it's called"
Result:
SG
Wayne - thanks for the reminder about the old , "if this then that" formula. I was hoping to avoid having to rewrite my formulas. I will also try the conditional highlighting solution offered by SG. That may also be a solution for some of my work sheets.
Thank you!
Scott Leverenz
SG,
I am wondering.... do you think conditional formatting is less compute intensive then the if() solution? I have not run a test but either works. The real deciding factor, to me, would be whether one is faster than the other.
Hmmm
Hi Wayne,
Compute time might be a factor in Numbers. I don't use it for large datasets so I'm not really sure about that. I don't notice much of a difference one way or the other in Excel.
I think I'd prefer the IF solution if the formulas are not already set up, since (for me) Conditional Highlighting in Numbers seems more involved than simply setting up a Custom Format in Excel (which is different from Conditional Formatting in Excel, of course).
But Conditional Highlighting in Numbers can save some work if the formulas already exist and one doesn't want to go in and change them.
SG
Most of my worksheets are pretty lightweight (i leave the heavy stuff to Filemaker) but i think i agree with SG, as i update any work sheets and/or make any mods, the IF calc will work just fine. I think both options my fit different situations. You guys rock. Thanks for the input.
--
Scott Leverenz
This got me thinking. It turns out that in Numbers it's easy to set up a Custom Format just as in Excel. No Conditional Highlighting needed.
Select the cells and choose 'Create Custom Format...':
Choose 'Add a Rule':
In the Rule choose 'If equal to' and 0, and clear out the format box beneath it so nothing is displayed.
Or, if wanted, put a - or whatever in the box, to give something like this:
This is likely to be less overhead than IF in formulas or Conditional Highlighting. May be the best solution in many situations.
SG
Zeros
