The
feature that custom number formatting (from now onward CNF) offers has been
discussed on various website in very detail. One of the webpage that I like
most is by Tom Niche that has a very detailed post on this feature of excel. In
this post I will explain how you should proceed with various features of CNF to
get a desired result.
For
the sake of elaboration I will take up few examples:
Example
01: INDIAN/PAKISTANI CURRENCY FORMAT
The
Asian subcontinent traditionally
measure monetary wealth in Thousand, Lacs, Carrors instead of Million, Billion
and Trillion Rupees. The difference become apparent when you write a number in
two different formats like below:
Amount
|
Indian
|
Non-Indian
|
100000/-
|
100,000/-
|
100,000/-
|
1000000/-
|
10,00,000/-
|
1000,000/-
|
10000000/-
|
100,00,000/-
|
10,000,000/-
|
100000000/-
|
10,00,00,000/-
|
100,000,000/-
|
The
difference between two can be observed while observing the placement of comma
(,) in the numbers. Up till amount of 0.1 Million, both follows same patron but
above this value, the Non-Indian System groups digits in bunch of three, while
the Indian system groups the first three digits (the thousandth part) into
three digits and remaining into the groups of two digit each. So the question
is how we are going to create a CNF that corresponds to our Indian Requirement.
Fortunately
most of the format has already been created by experts. We just need to
understand how they work, rather what is the principle that is used in their
making. Let’s start with our
requirement. We want to have a 7 digits number that follows the Indian format. For
our case can write this number this way:
#
|
#
|
\,
|
#
|
#
|
\,
|
#
|
#
|
#
|
Now
your custom format is ready for use. Try it on an excel sheet. But your CNF is
still not ready to use. If with this format you try to write a five digit
number, the format will place one comma before the number. For example if you
write 12500, it will appear as [,12,500]
so what we need to do is to place some sought of condition that can be checked for and then CNF applied. That
condition is added like this:
If a number is less
then 05 digits
|
####0.00
|
If a number is
greater then 05 digits
|
[>99999]##\,###0.00
|
If a number is
greater then 07 digits
|
[>9999999]##\,##\,###0.00
|
Putting
all this stuff together we can find reach this complete criterion.
[>9999999]#\,##\,###0.00; [>99999]#\,###0.00;
###0.00
|
Thus we have
reached our desired format.
But there is a
limitation to this process!! As I tried to add another level of detail to the
above CNF, Excel informed me that I need to try a built in format for that! So
what should then we can do it then? My suggestion is to make us of Text()
function in excel. The idea is to create a lookup table with desired output format
and criteria’s.
Situation
|
Criteria
|
Format
|
If a num > 03
digits
|
999
|
#\,##0.00
|
If a num > 05
digits
|
99999
|
#\,##\,##0.00
|
If a num > 07
digits
|
9999999
|
#\,##\,##\,##0.00
|
If a num > 09
digits
|
999999999
|
#\,##\,##\,##\,##0.00
|
Practically
it might not be possible to have a lookup table and do this stuff. This was
just meant for theoretical understanding. However this idea of having a table
can be handy when you want to check custom number formats of your own.
