Calculation Help

[milehibat]

Ok I know this is kind of gamer geek of me but I play WOW on a regular basis I have 5 characters and I sell all of my excess items in the auction house and then divide the profit among the 5 characters. I also ensure that the deposits paid by the one character remain with that character. I track all this using excel. I have been frustrated with one thing that I am hoping I can change in excel to help make the process easier. I have three columns one for Gold, one for Silver and one for Copper. In the game 100 Copper = 1 Silver and 100 Silver = 1 Gold. So here is my exact situation. I have the three columns for the deposit of 88 Silver and 56 Copper, I have a Total column that multiplies this by the times attempted to sell. Because things do not always sell the first time. So with this example the deposit has been paid twice so in a total deposit column I have 0 Gold, 176 Silver and 112 Copper. Which is the deposit price x 2. I am wondering if it is possible to have the amounts greater than 100 be calculated in the proper column. In this case my total deposit would read 1 Gold, 77 Silver and 12 Copper. I have tried everything I know to do and am at a loss hence my question here. Is there a formula I can use that will calculate this for me. Where I struggle is if the deposit were 1 Gold 88 Silver and 56 Copper multiplied times 2 to read 3, 88, 56 in each column, rather than 1, 176, 112. Please note that my current formula has the original deposit cell multiplied by the times listed.

I know this is kind of wordy but I am wanting it to make sense what I am trying to do.

Any and all help is welcome.

J.

 

[shyy]

Would help if you post up a sample of your data and the desired end result. You can create a lookup table for the currency but again with no data, I am not sure how to approach this.

 

[milehibat]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]G[/TD]
[TD]S[/TD]
[TD]B[/TD]
[TD]X Listed[/TD]
[TD]GT[/TD]
[TD]ST[/TD]
[TD]BT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]88[/TD]
[TD]56[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]176[/TD]
[TD]112[/TD]
[/TR]
</tbody>[/TABLE]

This is a crude representation of what I have currently. GT, ST, AND BT currently have the formula of =G*X Listed What I would like to see is

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]G[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]X Listed[/TD]
[TD]GT[/TD]
[TD]ST[/TD]
[TD]BT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]88[/TD]
[TD]56[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]77[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

A formula that would allow BT over 99 to be calculated as ST and ST that is over 99 to be calculated as GT. While still taking the original G, S, T and multiplying it by X Listed.

Does that make sense?

 

[shyy]

Not entirely sure, something like this?

Excel 2010
	A	B	C	D
1	G	S	B	X Listed
2	1	88	56	1
3	2	176	112	2
4	3	264	168	3
5	4	352	224	4
6	5	440	280	5
Sheet5

Cell Formulas
Range	Formula
A3	=A$2*$D3
A4	=A$2*$D4
A5	=A$2*$D5
A6	=A$2*$D6
B3	=B$2*$D3
B4	=B$2*$D4
B5	=B$2*$D5
B6	=B$2*$D6
C3	=C$2*$D3
C4	=C$2*$D4
C5	=C$2*$D5
C6	=C$2*$D6
D3	=D2+1
D4	=D3+1
D5	=D4+1
D6	=D5+1

 

[Dan777]

It'd be easiest to convert everthing to copper and then distribute as needed.
The final total is a lot simpler and you mess with only one variable.

 

[Andrew Poulsom]

Maybe:

Excel 2010
	A	B	C	D	E	F	G
1	G	S	B	X Listed	GT	ST	BT
2	1	88	56	2	3	77	12
Sheet4

Cell Formulas
Range	Formula
E2	=SUM(A2*D2,INT(B2*D2/100),INT(C2*D2/10000))
F2	=MOD(SUM(B2*D2,INT(C2*D2/100)),100)
G2	=MOD(C2*D2,100)

 

[milehibat]

Not quite what I am looking for but to use the data you supplied is there any way to make row 6 calculate anything over 99 in C6 to B6 and in the same respect anything over 99 to A6 So that row 6 would be A6= 9 B6= 42 and C6 = 80 ? It is kind of like dealing with money where 100 pennies = 1 dollar so that when you have anything over .99 it will automatically calculate in the dollar column. In this case I am dealing with Gold, Silver, Bronze. 100 Bronze = 1 Silver 100 Silver = 1 Gold and there is no limit on Gold amount.

 

[milehibat]

Andrew Poulsom THANK YOU SO MUCH, that is exactly what I needed. It works perfectly.

I knew it could be done I just needed help getting there.

J.