Excel, create a view that turns a number into gold, silver, and copper

I have an excel spreadsheet that I use to calculate money for a game.  The currency is like this:
100 copper = 1 silver
100 silver = 1 gold

I want to have this "1076.9" display as "10g 76s 9c".

I have tried a bit to find a way to do it but I come up short.  Ideas?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
format your cell on this custom #0"g" #0"s" 00"c"
0
Author Commented:
I think that is some of what I tried to do.  A part of it works.  For instance the cell now has "6698.65" in it.  After formatting it like that it shows "0g 66s 99c".

0
Commented:
can you use this custom format then

[>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"
0
Author Commented:
With that one "6698.65" shows as "66s 99c".
0
Commented:
then for this amount 6698.65  how exactly it should show?  so that i make the format for you
0
Author Commented:
It should show "66g 98s 54c".

More examples:
4019.9 = 40g 19s 9c
.48 = 48c
1.79 = 1s 79c

I hope that helps, and thanks!
0
Commented:
in this case, you cannot achieve this by using custom format for this one you need somewhat a sophisticated formula like this one.

put the amount in cell A1 and then put the below formula in B2 and then if there are many cells then just copy formula down.

``````=CONCATENATE(FLOOR((A1-(FLOOR(A1/10000, 1)*10000))/100, 1),"g ",INT(MOD(A1, 100)),"s ",INT(MOD(A1,1)*100),"c ")
``````

plz see attached workbook example
Book1.xlsx
0
Author Commented:
Getting real close.  Seems like Excel would be able to expand your initial thought to allow more digits, would be so much easier.

If I put 5459.5 (54g 59s 5c) it will calculate 54g 59s 50c.  Note it shows 50c instead of 5 copper.

I guess I am coming to realize as well that I might should just stick with using what I had been using, but it would just be so much better showing the values like your coming up with.
0
Author Commented:
Ah I see now.  I can just put 531.02 for 2 copper.  I can just put 531.2 for 20 copper.  That can work.
0
Commented:
try with this one. it will fix the above mentioned trailing zero issue

use either of the formulas, the previous one i provided or this one.

i think the above one , gives you more precise detail. but depends on what you want to do with it.

``````=CONCATENATE(FLOOR((A1-(FLOOR(A1/10000, 1)*10000))/100, 1),"g ",INT(MOD(A1, 100)),"s ",SUBSTITUTE(TRIM(SUBSTITUTE(INT(MOD(A1,1)*100),"0"," "))," ","0"),"c ")
``````
0
Author Commented:
Yeah, I'll use your first one.  The second one doesn't like when I put in 50c, since Excel makes .50 to 5 by default.  Plus you said it more precise.

So now my sheet, will have a bunch of related cells for the formula hey?  Here is my sheet for reference.
0
Commented:
ok i have modified the formula a bit to become more precise.

attached is the version with my first formula.
0
Author Commented:
Got it.  I noticed that if I put more than 1000 it doesn't show correctly. If I put 13,209.09 it comes out to be :32g 9s 1c.
0
Commented:
ok this part is also fixed with modification of formula.

``````=CONCATENATE(FLOOR((A1-(FLOOR(A1/10000000000, 1)*10000000000))/100, 1),"g ",INT(MOD(A1, 100)),"s ",SUBSTITUTE(TRIM(SUBSTITUTE(CEILING(INT(MOD(A1,1)*100),2),"0"," "))," ","0"),"c ")
``````

see plz attached file.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
You have helped with what I have wanted for sure.  In the end I can't see how I can keep a clean spreadsheet and have it easy enough to format the numbers like so.  If I want to make a list of materials and calculate those I have to basically have two columns, one with the excel way, then another column with the formula.

I have been playing with is some more, even with trying to put the formulated cells in another sheet.  Its getting to be too much work to do just a little bit of what I want to do.

I am accepting the solution and thanks for all of your help.
0
Author Commented:
User was timely and did just what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Computer Games

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.