Nov 11 2020 01:42 PM
im trying to get a formula together to help me with tracking my currency on my first dnd campaign. i have a formula that helps for a standard format however the campaign that im in has a bit of a different currancy system.
its measured in copper silver and gold and the standard metric is that
100 copper = 1 silver, 100 silver = 1 gold, this formula that i have does this for me by giving me a custom currency
[<100]#"c";[>10000]#"g "##"s "##"c";#"s "#"c";
but for the campaign that im in its
10 copper = 1 silver, 10 silver =1 gold. and for the life of me i cannot work out or find anything that can do this for me.
please if anyone kons how to do this then could you please help.
Nov 11 2020 01:53 PM
First, I'm not sure why it is that you can make that first formula work, but not the second, since it's just a difference of a factor of 10.
That said, your first formula doesn't appear to be any recognizable (at least not recognizable by me) Excel formula. If you actually have a spreadsheet where that's working, please share it. That way we can help you without having to read your mind.
Second, are there units of currency that reflect fractions of these different metals, much as our quarters or dimes or nickels represent 1/4, 1/10th and 1/20th of a dollar (which at one time reflected a certain number of ounces of silver)?
Said another way, how do you receive or spend this currency? What, other than figuring out the relationship between x amount of copper and the relevant amounts of silver or gold, are you tracking or using?
Said another way, give a bigger picture here of how this is fitting in to some larger context.
Nov 11 2020 01:55 PM
@mathetes i put it into the custom currency section the then makes the cell appear 00g 00s 00c
Nov 11 2020 01:58 PM
@mathetes heres the sheet
Nov 11 2020 02:14 PM
I'm sorry, but you don't say where that "custom currency section" is, and there's no section so labelled (as this image shows)
I did find a formula in cell B26 of "Sheet1" that converts the number in the cell above it to the format you are referring to, but haven't found it referenced in any other cells. It may be that the formula in that cell is the one needing to be changed.....
Are you thinking that DnD is something everybody has experience with?
I'd be happy to help, but you need to provide greater clarity in what is and what is not working.
Nov 11 2020 02:21 PM
@mathetes im sorry im a huge novice to a lot of this stuff, so ive put it in the custom category under format cell
Nov 11 2020 02:40 PM
Well, I'd say that's a hard way to do it. It's also not really dealing with the math itself, just the way it looks.
The formula in Sheet1, cell B27, is the one you need to work with, and I'll have to acknowledge it's a pretty sophisticated formula as well. But IT's where I would focus if I were you.
From whom did you get the spreadsheet in the first place? Is it a standard DnD template?
Nov 11 2020 02:40 PM
@mathetes the best i can compare it to would be the daim.
so 10 cents makes a daim and then 10 daims make a dollar
i want to format the cells currancy to be like this however instead
1 cent = 1 copper
1 daim = 1 silver
1 dollar = 1 gold
Nov 11 2020 02:44 PM
@mathetes a friend of mine was working on that but he couldnt finnish it so i have no clue what it means haha. template is for me ive looked at others and im not a fan of them so i though that id make my own for myself so that i could make it easier for me. this shop part is just something that i wanna work on for the future incase i ever make a world myself.
Nov 11 2020 03:04 PM
So let's go back to the drawing board here. I do notice that the one formula that I've referenced takes a number that represents a value and converts that, by means of an elaborate formula that concatenates numbers and text to produce what is really a result in text. It contains numbers, but is in fact text.
Copper is only reflected in whatever is in the original value after a decimal place.
Otherwise, a unit of 1 is 1 silver.
So a currency figure of 55 would be 55 silver.
a figure of 55.55 would be 55 silver and 55 copper
and so forth.
But the reason you're not able to do it with custom formatting (I think) is that you're not dealing with the underlying math there, only with the cosmetic, the way it looks.
The formula in sheet1, cell B27 deals with the numbers themselves. I think there's an easier, more accessible way than that.... a different approach, using a table and the INDEX and MATCH functions......I'll play around a bit to see what I can come up with, but it may not be this evening.
Nov 11 2020 03:10 PM
@mathetes Amazing thankyou very much
Nov 12 2020 07:22 AM
OK. I've come up with a way to do this. I'm going to let you take it from here. This ended up being both simpler and, in some ways, trickier that I expected.
It turns out that given the two alternatives a simple figure representing the ratio or multiplier used in the two currency systems was all you really needed. It's either 100, in the traditional method, or 10, in the variant in the game you're following.
Then you just need formulas that can take a figure like 567.8 or 5678.9 and convert those into corresponding numbers of Gold, Silver and Copper, the copper always reflected in the figures after the decimal place.
So check out "Sheet1" in the attached. I'm going to let you take it from here in terms of applying the methods to whatever actual transaction tracking you want to do. This should give you the tools, but you still have work to do, which will help you learn Excel. I prefer leaving it like that rather than giving you something that will work but be "a black box" to you.
Some suggestions, just for the sake of clarity:
Feel free to come back with questions, but see how much you can do on your own. A good learning resource is this website: https://exceljet.net/
You can research the various functions, as well as the notion of a "named range" which is what I've done with "Ratio"
Nov 12 2020 08:29 AM
@mathetes okay thank you ill try this out than kyou