Custom Currency/Numerical Formatting?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Info: I am trying to work with spreadsheets which contain numerical currency
found in a gaming application.

The format of their currency is ------gold --silver -- copper. Due to this
formatting, I would like to be able to further distinguish my numbers in the
spreadsheet presentation. This would be done by showing XXXXX.XX.XX,
utilizing two decimals.

I'm unsure of how to go about adding a custom formatting for dual-decimals,
while still retaining the ability to perform math on the number. For all
purposes, it's okay to store a number such as 1 gold 35 silver 20 copper as
13520 on the backend, as long as Excel shows this number as 1.25.20 to the
users' eyes. Since the silver and copper never goes above 99, it's the same
thing as 13520.

Question: Is there a way to create a custom currency formatting with retains
mathematical ability, but displays two decimals? If so, how would I do this?
 
Note that performing math on the number as you suggest storing it
would be meaningful only if 1gold=100silver, 1silver=100copper.

HTH
Kostis Vezerides
 
John

Perhaps a Custom Format of ###\.##\.##

1234567 returns 123.45.67 and remains a valid number of 1234567


Gord Dibben MS Excel MVP

On Fri, 23 Feb 2007 11:11:10 -0800, John Mann <John
 
Precisely, but this is the case in the situation I referred to. In other
applications it could be different.

vezerid said:
Note that performing math on the number as you suggest storing it
would be meaningful only if 1gold=100silver, 1silver=100copper.
Click to expand...

How would using #'s differ from using the 0's suggested above?
 
John

There is a difference but I don't think it applies in this case.

Explanation from Microsoft on controlling Custom Formats.

http://support.microsoft.com/kb/264372

Format Symbol Description/result
------------------------------------------------------------------------

0 Digit placeholder. For example, if you type 8.9 and
you want it to display as 8.90, then use the
format #.00

# Digit placeholder. Follows the same rules as the 0
symbol except Excel does not display extra zeros
when the number you type has fewer digits on either
side of the decimal than there are # symbols in the
format. For example, if the custom format is #.## and
you type 8.9 in the cell, the number 8.9 is
displayed.

Gord
 
Back
Top