1

Me and a friend are trying to make a spreadsheet in excel that can help us with displaying numbers as the currencies used in MMO games.

We are trying to create this spreadsheet as a help for selling and buying items in Guild Wars 2. As currencies in the game does not use number formatting as Euro and Cents etc. But instead has a format of Gold Silver and Copper, we are trying to make a custom value format that can hold this information.

So far we have something useable but we would like to make it a bit more manageable.

Our format is currently: #"G "##"S "##"C"

This works decently but we would like it so that G and S will not be displayed if we only have, say, 80 copper. Further, we were also wondering if it is possible for us to color code each subvalue with a different color. I know it is possible to do it so the whole value if we have a negative value, but as far as i understand it is not possible to color individual parts of the number in a cell.

Does anyone know if this is possible to do ?

Ulrik Kroge Sloth
  • 51
  • 7
  • Is it OK if the result is a string, or do you need to be able to perform arithmetic on a cell that contains such a values? – lurker Mar 05 '15 at 19:59
  • I need to do some calculations. The idea is that i have to be able to easily type in a price of an item, and then it will calculate the cost of crafting it, reselling it etc. There will be some costs to whatever method im gonna use and i need to subtract these values. – Ulrik Kroge Sloth Mar 06 '15 at 10:02

3 Answers3

0

You are on the right track, you can specify conditionals by using bracket []:

Example: Color numbers less than or equal to 2000 RED and numbers greater than 3000 GREEN and stuff between 2000 and 3000 BLUE would translate to:

[Red][<=2000]#,##0" Reds";[Green][>3000]#,##0" Greens";[Blue]#,##0" Blues"

Custom_Formatting

You apply this custom formatting by doing Right+Click, Format Cells, and under Category choose custom and enter the above code. The #,##0 is to comma separate the values above 1,000. Tweak and enjoy.

JasonAizkalns
  • 20,243
  • 8
  • 57
  • 116
  • This is kind of what i knew already. The thing is, the number should optimally be placed in one cell. Right now i can type in eg. 108402 and i will get anumber as: "10G 84S 2C" and that is as it should be. The problem is if i for instance has less than one gold eg. 4502. Then i get the number " G 45S 2C " What i intend is for the G to disappear if the value is not high enough. The color coding would be nice, but i think you can only color the full number and nt just parts through cell formatting? – Ulrik Kroge Sloth Mar 06 '15 at 10:10
  • Hmm...interesting. Not sure if that's possible. You may want to update your original question with that logic and desired result. One way would be to split this up into three columns. Type the number in one cell and conditionally format the result across three columns. – JasonAizkalns Mar 06 '15 at 14:05
0

You may want to use two columns, one to store the numeric value and another to store the display value

In the display column, use a formula or udf to alter the value add needed, eg avoid a leading g

Another option is to for all values less than 1G to been with 00 G

spioter
  • 1,829
  • 1
  • 13
  • 19
0
[<100]#"c";[>10000]#"g "##"s "##"c";#"s "##"c";

Just put the above into excels custom format. but just be aware if you have zero value, there will be a single c in the column as replacement.

user3015483
  • 1