[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 custom number formats

Posted on 2011-10-12
9
Medium Priority
?
433 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:biker9
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36957803
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
 
LVL 81

Expert Comment

by:byundt
ID: 36958050
You can make the negative sign appear in white with:
[<-1](General);[>0] General;[White]
0
 
LVL 81

Expert Comment

by:byundt
ID: 36958069
Note that there is a trailing space character after [White] in my Custom format
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 50

Expert Comment

by:barry houdini
ID: 36958071
Ah, very nice Brad!
0
 

Author Comment

by:biker9
ID: 36959006
Unfortunately the cell backgrounds vary in colour, the white or other colour shows the character.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36959051
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
 

Author Comment

by:biker9
ID: 36959124
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36959171
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
 

Author Closing Comment

by:biker9
ID: 36959238
Barry, that's Perfect!

Thanks again,
Ray
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question