Excel 2010 custom number formats

I need a custom number format that can "hide" values between 0 and negative 1, and not display zeros, - signs or brackets. Values less than negative one need to be displayed within brackets. Ie: value of --4.22915036324412E-12 would be a blank, value of -4 would show as (4), etc.

Conditional formatting is not an option.

Appreciate the help!
biker9Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
OK, Ray, no problem

If the numbers you want to hide are "almost zero" perhaps you could alter the formulas by adding ROUND (to a large number of decimal places, to make those into zeroes, e.g.

=ROUND(your_formula,10)

Then format to show just zero as blank, e.g.

General;(General);

regards, barry
0
 
barry houdiniCommented:
Closest I can get is this custom format

[>0]General;[<-1](General);

Unfortunately that display numbers between 0 and -1 as a dash "-", others should be as you requested - perhaps it's achievable but I couldn't manage it......

regards, barry
0
 
byundtCommented:
You can make the negative sign appear in white with:
[<-1](General);[>0] General;[White]
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
byundtCommented:
Note that there is a trailing space character after [White] in my Custom format
0
 
barry houdiniCommented:
Ah, very nice Brad!
0
 
biker9Author Commented:
Unfortunately the cell backgrounds vary in colour, the white or other colour shows the character.
0
 
barry houdiniCommented:
Well, as per my first answer, I'm not sure you can achieve this with formatting alone.

You say conditional formatting is not an option - why not? You can apply a number format via conditional formatting in Excel 2010 so you could apply a number format of

;;;

which displays everything as blank, conditionally based on the cell content being between 0 and -1. The other conditions can be achieved by the regular cell formatting.

If that's not an option....how are your numbers generated by formula or some other way?

regards, barry
0
 
biker9Author Commented:
I've got a large spreadsheet, conditional formatting in excel 2010 (though better than previous versions) slows down the performance, I'm trying to keep everything optimized and also "clean" in appearance. The numbers are generated by formulae, so sometimes very small negative irrelevant numbers pop up.

Very much appreciate your comments.

Ray
0
 
biker9Author Commented:
Barry, that's Perfect!

Thanks again,
Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.