我正在尝试为我的D＆amp; d幻想货币设置Excel。
我想使用Excel将整数和小数转换为黄金（GP），银（SP）和铜（CP）件；例如25.47 = 25GP 4SP 7CP。
转换非常简单，如在此转换表上或下面看到的那样。
1Sp = 10cp
1gp = 10sp = 100cp
i从答案中获得此代码，该答案 user4039065 以 juddson ivines - 如何以microsoft excel中的幻想货币格式化
？ ;;;“）＆amp; text（ - 右（text（j6，“ 0.0”）），“ 0 \ s \ p ;;;”） ），“ 0 \ c \ p ;;”））
乍一看，此代码效果令人惊讶。
但是，在将0.6转换为6CP时，代码弄乱并回答为 1cp 6sp 。由于某种原因，代码正在添加1Sp。
在
我什至从1CP到超过2GP播放以检查错误，您可以请参阅此处。这就是我遇到0.95错误的方式。
感谢 user4039065 的答案和开始代码，以及 提出原始问题。
很抱歉在这里成为新手，而且我无法嵌入我的图像。
我真的很感谢您纠正此代码格式的一些帮助。
I am trying to set up Excel for my D&D Fantasy Currency.
I want to use Excel to convert integers and decimals to gold (gp), silver (sp), and copper (cp) pieces; e.g. 25.47 = 25gp 4sp 7cp.
The conversion is fairly simple, as seen on this conversion table or below.
1sp = 10cp
1gp = 10sp = 100cp
I got this code from an answer that user4039065 posted on as similar question asked by Juddson Ivines - How to format fantasy currency in Microsoft Excel?:
=TRIM(TEXT(INT(J6),"0 \g\p ;;;")&TEXT(--RIGHT(TEXT(J6,"0.0")),"0 \s\p ;;;")&TEXT(--RIGHT(TEXT(J6,"0.00")),"0 \c\p ;;;"))
This code at first glance worked amazingly.
However when it came to converting 0.6 to 6cp, the code messes up and answers as 1cp 6sp. For some reason the code is adding 1sp.
A further error occurs at 0.95-0.99, where it drops the 9sp entirely.
I even gridded out from 1cp to over 2gp to check for errors, which you can see here, which is how I caught the 0.95 error.
Thanks to user4039065 for their answer and starting code, as well as Juddson Ivines for asking the original question.
Apologies for being new here and my lack of being able to embed my images.
I would really appreciate some help with correcting the format of this code.
发布评论
评论（1）
C列中的公式为：
您可能会从几个功能中受益。棘手的部分是获得每种类型的硬币的IF，并且还避免铜硬币中的左零（这是价值义务）
有时您可能会因小数而获得怪异的输出。您只需要将值弄圆。
Formula in column C is:
You may benefit from several functions to achieve this. The tricky part is to get an IF for each type of coin and also avoid left zeros in the copper coins (that's the VALUE duty)
Probably sometimes you may get weird output due to decimals. You just need to Round the values.