Solved

Custom format to show numbers in thousands, brackets and red for negative numbers

Posted on 2014-12-06
5
6,595 Views
Last Modified: 2014-12-09
hi Folks
What custom format do I need to use to get the above i.e.
I want
100,000 to appear as 100
-25,000 to appear as (25) (in red)
Thank you
0
Comment
Question by:agwalsh
5 Comments
 
LVL 91

Assisted Solution

by:John Hurst
John Hurst earned 100 total points
ID: 40484611
You can use Format Cells, Number and select brackets, red, separators, and rounding 0, but that will not change the number itself.

To show 25,000 as 25 you need to divide by 1000 and then round to 0.
0
 
LVL 5

Assisted Solution

by:Hakan Yılmaz
Hakan Yılmaz earned 100 total points
ID: 40484618
You can look here for cell format examples.

This is your case:
# ;[Red](# )
or this;
0 ;[Red](0 )

It may depend on your local decimal or thousand seperator settings. My thousand sepetator is "space" character.
If your thousand seperator is comma "," character, this formulas will be;

#,;[Red](#,)
and
0,;[Red](0,)
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 250 total points
ID: 40485410
Several examples of displaying as Thousands and Millions.  More examples in the attached file

Thousands
#,##0,;[Red](#,##0,)            
       100,000       =      100
        (25,000)      =      (25)

Thousands      
#,##0.00,;[Red](#,##0.00,)            
       100,000       =      100.00
        (25,000)      =      (25.00)

Thousands      
#,##0,k;[Red](#,##0,k)            
       100,000       =      100k

Millions      
#,##0,,;[Red](#,##0,,)            
       100,000,000       =      100

Millions      
#,##0.00,,;[Red](#,##0.00,,)            
       100,000,000       =      100.00

Millions      
#,##0.00,,"M";[Red](#,##0.00,,"M")            
       100,000       =      0.10M
Q-28575563.xlsx
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 50 total points
ID: 40486598
Thinking beyond your initial question, where are you wanting to use this Custom Format?

If it is on a Summary report where the source data is in single units but the Summary is reported in thousands, why not just divide the formula pulling source data by 1000. Or set a cell to show 1, 100, 1000 or 1000000 and then you can use that as the Factor value and have all cells divided by it.

If its for showing on a graph, you can set the Axis label values to show as factored by thousands or millions etc.

Thanks
Rob H
0
 

Author Closing Comment

by:agwalsh
ID: 40488359
Did exactly what I wanted...thank you :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now