Carrying over values over 99 to another column

[asmi]

My spreadsheet tracks incoming and outgoing values of 3 items: Gold, Silver, Copper. Columns D-F represent the adds/subs of each item, while columns G-I keep a running tally of each using formulas. However, the I need the values for silver and copper to max out at 99 while adding any additional value to the appropriate column.

100 copper is equal to 1 silver, so if the formula for cell I10 (running total of copper) results in a value of 100, I want I10 to be 0 while adding 1 to H10 (running total of silver). The same goes for silver, if the value for silver is at 100, it should add 1 to the value for gold and reduce the silver running total to 0.

For example, 100 gold, 100 silver, 100 copper = 101 gold, 1 silver, 0 copper.

How can I do this?

 

[Yogi Anand]

My spreadsheet tracks incoming and outgoing values of 3 items: Gold, Silver, Copper. Columns D-F represent the adds/subs of each item, while columns G-I keep a running tally of each using formulas. However, the I need the values for silver and copper to max out at 99 while adding any additional value to the appropriate column.

100 copper is equal to 1 silver, so if the formula for cell I10 (running total of copper) results in a value of 100, I want I10 to be 0 while adding 1 to H10 (running total of silver). The same goes for silver, if the value for silver is at 100, it should add 1 to the value for gold and reduce the silver running total to 0.

For example, 100 gold, 100 silver, 100 copper = 101 gold, 1 silver, 0 copper.

How can I do this?

Hi asmi:

Welcome to MrExcel Board!

Let us have a look at ...

Book1
	D	E	F	G	H	I
1	Gold	Silver	Copper	Gold	Silver	Copper
2	100	100	100	101	1	0
3
Sheet4

Is this what you are looking for?

 

[asmi]

This is a great start! Ok, now when you add more values in Cols D-F of Row 3, what becomes of the running total in Cols G-H? They will need to include the previous total of G2:H2 as well as adding the new amounts in D3:F3. And so on... Also, does this work if the values of Cols D-F are negative?

Thanks very much! Can you brief explain the formulae you are using? MOD and INT?

EDIT: I guess it might help to mention this represents an accounts sheet for funds. Only the funds are not in dollar amounts, but similar with an additional representation. 1 Gold=100 Silver, 1 Silver=100 Copper. I am tracking income and expenses to this fund comprised of gold, silver, and copper.

 

[Yogi Anand]

Hi asmi:

INT function retains only the integer value of an operation, for example ...

=INT(201/100) will result in 2

and MOD function retains only the remainder value of the operation ... for example

=MOD(201,100) will result in 1

As far as the other part of your question, I suggest you utlize these (INT and MOD) functions in your calculations along with other needed functions such as the SUM function, play with it and post back with details and specifics.

 

[erik.van.geit]

Hi, guys,
while you were posting, I was searching for solution
perhaps it can be done shorter ?
anyway this seems to work
DEF columns are sourcedata
GHI columns formulas as you need
G3: =SUM(D$3:D3)+TRUNC((SUM(E$3:E3)+SUM(F$3:F3)/100)/100)
H3: =MOD(SUM(E$3:E3),100)+TRUNC(SUM(F$3:F3)/100)
I3: =MOD(SUM(F$3:F3),100)
KLM columns just summed to check if results are OK

Map3.xls
	D	E	F	G	H	I	J	K	L	M
1	data			added				check
2	gold	silver	copper	gold	silver	copper		gold	silver	copper
3	50	20	10	50	20	10		50	20	10
4	10	80	40	61	0	50		60	100	50
5	40	80	50	101	81	0		100	180	100
6	10	30	80	112	11	80		110	210	180
7	10	90	90	123	2	70		120	300	270
Blad1

kind regards,
Erik

 

[asmi]

Hi, guys,
while you were posting, I was searching for solution
perhaps it can be done shorter ?
anyway this seems to work
DEF columns are sourcedata
GHI columns formulas as you need
G3: =SUM(D$3:D3)+TRUNC((SUM(E$3:E3)+SUM(F$3:F3)/100)/100)
H3: =MOD(SUM(E$3:E3),100)+TRUNC(SUM(F$3:F3)/100)
I3: =MOD(SUM(F$3:F3),100)
KLM columns just summed to check if results are OK

kind regards,
Erik

That works absolutely perfectly! Thank you so much.

And thanks for helping me understand new functions I didn't know. Wish Excel's help files were written by you guys.

 

[Yogi Anand]

Using the initial data as Erik has proposed, here is my take on it ...

Book1
	A	B	C	D	E	F	G	H	I	J
1				gold	silver	copper	gold	silver	copper
2				0	0	0	0	0	0
3				50	20	10	50	20	10
4				10	80	40	61	0	50
5				40	80	50	101	81	0
6				10	30	80	112	11	80
7				10	90	90	123	2	70
8
Sheet7

formula in cell I3 is ... =MOD(F3+I2,100)

formula in cell H3 is ... =MOD(E3+H2,100)+INT(SUM(F$2:F3)/100)-INT(SUM(F$2:F2)/100)

formula in cell G3 is ... =D3+G2+INT(SUM(E$2:E3)/100)-INT(SUM
(E$2:E2)/100)

these are then copied down.

I have not checked whether these formulas can be further simplified!

 

[erik.van.geit]

you're welcome
but please TAKE CARE: when you need negative numbers, you'll get into trouble with these formulas
don't want to break my head now on this (time to sleep); but the results are "strange"

best regards,
Erik

 

[asmi]

you're welcome
but please TAKE CARE: when you need negative numbers, you'll get into trouble with these formulas
don't want to break my head now on this (time to sleep); but the results are "strange"

best regards,
Erik

I replaced my formulas with yours and the result (including deductions) came out exactly the same with about 15 lines entered at the moment. So far, so good.

Thanks again to all who helped.

 

[erik.van.geit]

I meant "when the result is negative" then you get strange results
with mine and as well as with Yogis formulas

Map3.xls
	D	E	F	G	H	I
1	gold	silver	copper	gold	silver	copper
2	0	0	0	0	0	0
3	-10	-10	-10	-11	89	90
Blad1