Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Currency/Numerical Formatting?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Currency/Numerical Formatting?
try
0\.00\.00 HTH "John Mann" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Currency/Numerical Formatting?
Thanks, that worked wonderfully. I had no idea about the backslashes.
"Toppers" wrote: try 0\.00\.00 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Currency/Numerical Formatting?
Note that performing math on the number as you suggest storing it
would be meaningful only if 1gold=100silver, 1silver=100copper. HTH Kostis Vezerides On Feb 23, 9:11 pm, John Mann <John wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Currency/Numerical Formatting?
Precisely, but this is the case in the situation I referred to. In other
applications it could be different. "vezerid" wrote: Note that performing math on the number as you suggest storing it would be meaningful only if 1gold=100silver, 1silver=100copper. How would using #'s differ from using the 0's suggested above? "Gord Dibben" wrote: Perhaps a Custom Format of ###\.##\.## 1234567 returns 123.45.67 and remains a valid number of 1234567 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Currency/Numerical Formatting?
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 On Fri, 23 Feb 2007 15:02:02 -0800, John Mann wrote: Precisely, but this is the case in the situation I referred to. In other applications it could be different. "vezerid" wrote: Note that performing math on the number as you suggest storing it would be meaningful only if 1gold=100silver, 1silver=100copper. How would using #'s differ from using the 0's suggested above? "Gord Dibben" wrote: Perhaps a Custom Format of ###\.##\.## 1234567 returns 123.45.67 and remains a valid number of 1234567 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting custom currency quirk | Excel Discussion (Misc queries) | |||
Custom Formatting For Multiple Currency Symbols | Excel Discussion (Misc queries) | |||
Comma separator for custom Currency | Excel Discussion (Misc queries) | |||
convert a numerical currency into word format | Excel Worksheet Functions | |||
currency custom fomula | Excel Discussion (Misc queries) |