Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
HI,
If I have a series of numbers in Cell A1: A10 like 156789 etc I want to be able to do 2 kinds of operations:- a) How to set the format, so that number gets displayed as 156.789 or let' say 157 . I have seen folks using custom formats but not sure how to set it up in this case. b) How to set the format in such a way that we may also be see "157 Thousand" for above case. Is it possible using custom formats. (I will like to do the same thing for 1234567 as "1.2 Million" and so on) Please guide me. Regards, HP India |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
This isn't a formatting problem. The data should be divided by 1000, THEN you deal with the presentation (such as rounding)! -- raypayette ------------------------------------------------------------------------ raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569 View this thread: http://www.excelforum.com/showthread...hreadid=563634 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
"Hari" skrev i en meddelelse
oups.com... HI, If I have a series of numbers in Cell A1: A10 like 156789 etc I want to be able to do 2 kinds of operations:- a) How to set the format, so that number gets displayed as 156.789 or let' say 157 . I have seen folks using custom formats but not sure how to set it up in this case. b) How to set the format in such a way that we may also be see "157 Thousand" for above case. Is it possible using custom formats. (I will like to do the same thing for 1234567 as "1.2 Million" and so on) Please guide me. Regards, HP India Hi HP Apparently you have "." as a thousand separator. To format as 156.789 use the userdefined format #.# To format as thousands add a point to the right of the format #.#. or #.#. "Thousand" to format as millions add 2 points #.#.. or #.#.. "Million" #.#... or #.#... "Billion" etc. Add one point for each "thousand group" -- Best regards Leo Heuser Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
This *can* be accomplished with custom formatting:
[<=999999]#.#," Thousand";[999999]#.#,," Million";General -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "raypayette" wrote in message ... This isn't a formatting problem. The data should be divided by 1000, THEN you deal with the presentation (such as rounding)! -- raypayette ------------------------------------------------------------------------ raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569 View this thread: http://www.excelforum.com/showthread...hreadid=563634 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
Leo,
Thanx a lot. It works I just wanted to know as to whether it is also possible to express in "Hundreds" etc. Regards, HP India Leo Heuser wrote: "Hari" skrev i en meddelelse oups.com... HI, If I have a series of numbers in Cell A1: A10 like 156789 etc I want to be able to do 2 kinds of operations:- a) How to set the format, so that number gets displayed as 156.789 or let' say 157 . I have seen folks using custom formats but not sure how to set it up in this case. b) How to set the format in such a way that we may also be see "157 Thousand" for above case. Is it possible using custom formats. (I will like to do the same thing for 1234567 as "1.2 Million" and so on) Please guide me. Regards, HP India Hi HP Apparently you have "." as a thousand separator. To format as 156.789 use the userdefined format #.# To format as thousands add a point to the right of the format #.#. or #.#. "Thousand" to format as millions add 2 points #.#.. or #.#.. "Million" #.#... or #.#... "Billion" etc. Add one point for each "thousand group" -- Best regards Leo Heuser Followup to newsgroup only please. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
RagDyer,
Thanks for conditional number formatting. Its a nice tool to have. Regards, HP India RagDyeR wrote: This *can* be accomplished with custom formatting: [<=999999]#.#," Thousand";[999999]#.#,," Million";General -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "raypayette" wrote in message ... This isn't a formatting problem. The data should be divided by 1000, THEN you deal with the presentation (such as rounding)! -- raypayette ------------------------------------------------------------------------ raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569 View this thread: http://www.excelforum.com/showthread...hreadid=563634 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
You're very welcome.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Hari" wrote in message s.com... RagDyer, Thanks for conditional number formatting. Its a nice tool to have. Regards, HP India RagDyeR wrote: This *can* be accomplished with custom formatting: [<=999999]#.#," Thousand";[999999]#.#,," Million";General -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "raypayette" wrote in message ... This isn't a formatting problem. The data should be divided by 1000, THEN you deal with the presentation (such as rounding)! -- raypayette ------------------------------------------------------------------------ raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569 View this thread: http://www.excelforum.com/showthread...hreadid=563634 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show number in thouands using Formats
"Hari" skrev i en meddelelse
ps.com... Leo, Thanx a lot. It works I just wanted to know as to whether it is also possible to express in "Hundreds" etc. Regards, HP India Hari You're welcome and thanks for the feedback :-) Unfortunately its not possible to express in "Hundreds" etc. with formatting. Regards Leo Heuser |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force function to show positive or negative number? | Excel Worksheet Functions | |||
cell to show ' 0 ' , or nothing at all, if the sum is a negative number | Excel Worksheet Functions | |||
How do I show negative number in brackets using excel? | Excel Worksheet Functions | |||
Maximum Number of Cell Formats | Excel Discussion (Misc queries) | |||
number formats default to 2 decimal points when entered so 24 bec. | Excel Discussion (Misc queries) |