Help with totaling up currency for a game

[reaktorblue]

I'm trying to make a spreadsheet as a shopping list within a game I'm playing in my spare time but for the life of me, I'm not able to figure out how to add up the values such that it correctly matches the game's values.

100 copper = 1 silver
100 silver = 1 gold

An example can be found below. Is there a way to tally up those columns, so that the total's add up properly?

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#FFC000]

Total​

[/td][td=bgcolor:#FFC000]Gold[/td][td=bgcolor:#FFC000]Silver[/td][td=bgcolor:#FFC000]Copper[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#FFC000]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

98​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#FFC000]Item Name[/td][td=bgcolor:#FFC000]Gold[/td][td=bgcolor:#FFC000]Silver[/td][td=bgcolor:#FFC000]Copper[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td=bgcolor:#BDD7EE]Test Item 1[/td][td=bgcolor:#BDD7EE][/td][td=bgcolor:#BDD7EE][/td][td=bgcolor:#BDD7EE]

99​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Test Item 2[/td][td][/td][td]

1​

[/td][td]

99​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#BDD7EE]Test Item 3[/td][td=bgcolor:#BDD7EE][/td][td=bgcolor:#BDD7EE]

99​

[/td][td=bgcolor:#BDD7EE][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[DataBlake]

try this?

Book1
	A	B	C	D
1	Total	Gold	Silver	Copper
2		1	1	98
3
4	Item Name	Gold	Silver	Copper
5	it1			99
6	it2		1	99
7	it3		99
8		1.198	1.98	98
Sheet1

Cell Formulas
Range	Formula
B2	=ROUNDDOWN(B8,0)
B8	=(SUM(D5:D7)*0.001)+(SUM(C5:C7)*0.01)+SUM(B5:B7)
C2	=ROUNDDOWN(C8,0)
C8	=(B8-B2)*10
D2	=ROUNDDOWN(D8,0)
D8	=(C8-C2)*100

 

[reaktorblue]

This doesn't appear to work properly unless I did something wrong, which is entirely possible! Below is an example with only one item price of 99 silver. The expected total should be 99 silver however, the actual result appears to be 9 silver 90 copper

Excel 2016 (Windows) 64 bit
	A	B	C	D
1	Total	Gold	Silver	Copper
2		0	9	90
3
4	Item Name	Gold	Silver	Copper
5	it1		99
6	it2
7	it3
8		0.99	9.9	90
Sheet2

Cell Formulas
Range	Formula
B2	=ROUNDDOWN(B8,0)
B8	=(SUM(D5:D7)*0.001)+(SUM(C5:C7)*0.01)+SUM(B5:B7)
C2	=ROUNDDOWN(C8,0)
C8	=(B8-B2)*10
D2	=ROUNDDOWN(D8,0)
D8	=(C8-C2)*100

 

[reaktorblue]

So, I'm able to count the total number of copper based on each lines gold, silver, and copper with the formula in C3. Is there a way to convert the total copper into something, such that the "10200" in C3 would display 1g 2s 0c?

I've seen several similar formulas that look promising but they end up failing. Any ideas on how this could be accomplished?

Excel 2016 (Windows) 64 bit
	A	B	C	D
1	Total
2	Items	Amount	Copper
3	4		10200
4
5	Item Name	Gold	Silver	Copper
6	Test Item 1			99
7	Test Item 2			1
8	Test Item 3		1
9	Test Item 4	1
Sheet1

Cell Formulas
Range	Formula
A3	=COUNTA(tblItems[Item Name])
C3	=SUM(tblItems[Copper],(tblItems[Silver]*100),(tblItems[Gold]*10000))

 

[Fluff]

How about

Book1
	A	B	C	D
1	Total	Gold	Silver	Copper
2		1	1	98
3
4	Item Name	Gold	Silver	Copper
5	Test Item 1			99
6	Test Item 2		1	99
7	Test Item 3		99
8		0	100	198
Form

Cell Formulas
Range	Formula
B2	=MOD(B8,100)+INT((C8*100+D8)/10000)
B8	=SUM(B5:B7)
C2	=MOD(C8,100)+INT(D8/100)
C8	=SUM(C5:C7)
D2	=MOD(D8,100)
D8	=SUM(D5:D7)

 

[reaktorblue]

This one seems to work right up until something is 100 gold or more. As an example, if Test Item was 104 gold, it only displays 4

 

[Fluff]

I wasn't thinking.
The formula in B2 should be
=B8+INT((C8*100+D8)/10000)

 

[reaktorblue]

Outstanding! I think this is working now! Thank you so much, I really appreciate the help!!

 

[Fluff]

Glad we could help & thanks for the feedback