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?
getwidth28Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
format your cell on this custom #0"g" #0"s" 00"c"
getwidth28Author 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".

Any other thoughts please?
ProfessorJimJamMicrosoft Excel ExpertCommented:
can you use this custom format then

[>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

getwidth28Author Commented:
With that one "6698.65" shows as "66s 99c".
ProfessorJimJamMicrosoft Excel ExpertCommented:
then for this amount 6698.65  how exactly it should show?  so that i make the format for you
getwidth28Author 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!
ProfessorJimJamMicrosoft Excel ExpertCommented:
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 ")

Open in new window


plz see attached workbook example
Book1.xlsx
getwidth28Author 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.
getwidth28Author 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.
ProfessorJimJamMicrosoft Excel ExpertCommented:
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 ")

Open in new window

getwidth28Author 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.
Cost-of-Damask-if-to-make-money.xlsx
ProfessorJimJamMicrosoft Excel ExpertCommented:
ok i have modified the formula a bit to become more precise.

attached is the version with my first formula.
Cost-of-Damask-if-to-make-money.xlsx
getwidth28Author 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.
ProfessorJimJamMicrosoft Excel ExpertCommented:
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 ")

Open in new window


see plz attached file.
Cost-of-Damask-if-to-make-money.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
getwidth28Author 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.
getwidth28Author Commented:
User was timely and did just what I needed.
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.