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

x
?
Solved

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

Posted on 2014-12-06
5
Medium Priority
?
12,626 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 99

Assisted Solution

by:John Hurst
John Hurst earned 400 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 400 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 1000 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 34

Assisted Solution

by:Rob Henson
Rob Henson earned 200 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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

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…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

834 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