|

# » Rounding Numbers to Thousands with Hundreds as a Decimal

To round a number to thousands with the hundreds shown as a decimal:

1. Press Ctrl+1 to open the Format Cells dialog box.
2. Select the Number tab, and from Category, select Custom.
3. In the Type box, enter the following Custom Formatting syntax:
#,##0.0, ;[Red](#,##0.0,);- ;

Result:
• Original number: 5,645,625
• The displaying formatting number: 5,645.6

Note:
The number formatting syntax is:
Positive; Negative; 0; Text
Screenshot // Rounding Numbers to Thousands with Hundreds as a Decimal

Rate This Tip
 1 2 3 4 5
Rating: 3.62     Views: 64748
Number formats for India
Prashanth  Posted on: 31-12-1969
We format 5 million in India as 5,00,000, and 500 million as 50,00,00,000. How do we get this kind of number formatting in Excel?
Reply: Number formats for India - Prashanth from India wrote on August 2, 2003 6:44 AM EST
Alan  Posted on: 31-12-1969
Hi Prashanth,

I cannot understand the logic of how you say you format numbers in India.

How do you arrive at:

5,00,000 to represent 5 million?

Is there some kind of implied multiplication in the fact that the leading 5 is grouped on its own?

Also, it appears (to my English eyes) to be inconsistent with the formatting of 500 million as:

50,00,00,000

In this example, the removal of the commas would result in the same numerals as we would use in New Zealand (500000000) if you stripped the formatting.

This seems difference (to me) to the way in which 5 million is reduced to what I would expect to be five hundred thousand (500000) above.

Please can you elaborate on the Indian formatting system a little more, and hopefully we can find a custom format that works for you.

Alan.
Number Formats for India: Rs 1,23,45,678.00
Amit  Posted on: 31-12-1969
One Million is typically represented as 1,000,000. In India, we call it 10 lacs and represent it as 10,00,000. Similarly, 100Million is represented as 10,00,00,000.

How do we do this type of formatting in Excel?
Reply: Number Formats for India: Rs 1,23,45,678.00 - Amit from India wrote on August 13, 2003 8:57 AM EST
Alan  Posted on: 31-12-1969
Hi Amit,

However, I have to admit that I still cannot see how to generalise from the examples you and Prashanth have given.

For example:

If one million is represented as "10,00,000" then how can five million be represented as "5,00,000" (refer to Prashanth's original post above)?

I'm afraid this seems devoid of any logic to me. I do not mean to be offensive - apologies if I seem so.

However, in order to come up with a general custom format, we need to understand the logic behind the way you are representing the numbers. Without a logical (rule based) approach, there is no way that I can see to help you.

Thanks,

Alan.
Indian Number system
Joseph  Posted on: 31-12-1969
I just happened to visit your site and found the discussion quite amusing. I do agree that the Indian system can be confusing at first. The confusion is based on errors in typing and not in conception. To follow Prashant's logic 1 million= 10,00,000 or 10 lacs. It logically then follows that 5 millioin= 50,00,000 or 50 lacs. 10 million=100,00,000 or 100 lacs. To make matter more confusing the "crore" is another traditional unit of counting. 1 crore = 10 million. The reason one finds the system cumbersone is that the Indian system, to my knowledge, does not have an equivalent term for a million. The system is based on units, tens, thousands, lacs, crores, etc. but no million. I hope this helps.
Reply: Indian Number system - Joseph from San Bernardino wrote on August 19, 2003 10:20 AM EST
Alan  Posted on: 31-12-1969
Hi Jospeh,

You may have shed some light on this!

You state that:

"5 millioin= 50,00,000 or 50 lacs"

However, in his original post, Prashanth is quite explicit that:

"We format 5 million in India as 5,00,000"

Obviously you cannot both be correct, or if you are then Excel cannot possibly display both at once!

Joseph - Your email seems both internally consistent, and consistent with that of Amit.

Perhaps Prashanth is confused about how the numbers are represented?

Is anyone still interested in an answer to this? If so, please can you reply, setting out a few examples across all orders of magnitude from 10^1 to, say, 10^10 and be VERY CAREFUL about how you type.

Thanks,

Alan.

Ease Alan's Confusion
Hasen Akhtar  Posted on: 31-12-1969
Let's see if this helps:

Unit, Ten, Hundred, Thousand, (100 thousand) Lac, Ten Lacs (1 million), (100 Lacs) Crore (10 million), Ten Crore (100 million), (100 Crore) Arrab (1 billion), Ten Arrab,(100 Arrab) Kharrab, Ten Kharrab.

As you can see that the accounting in the Sub-Continent is based on 100-unit system. You get a new unit when it has 100 of the current units. Whereas in the western accounting system, 1-million is made of 10-units of 100-thousand. 1-billion is made of 1000-units of million.

I hope it is clear that
hasen
Reply: Ease Alan's Confusion - Hasen Akhtar from Chicago, IL wrote on August 31, 2003 11:25 AM EST
Alan  Posted on: 31-12-1969
Hi Hasen,

That helps quite a lot!

I would still like to see some definitive examlpes though.

How are the following represented:

5
10
15
50
100
150
500
1000
1500
5000
10000
15000
50000
100000
150000
500000
1000000
1500000
5000000
10000000
15000000
50000000

Thanks,

Alan.