# Carrying over values over 99 to another column

#### asmi

##### New Member
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?

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
asmi said:
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
DEFGHI
1GoldSilverCopperGoldSilverCopper
210010010010110
3
Sheet4

Is this what you are looking for?

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.

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.

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
DEFGHIJKLM
2goldsilvercoppergoldsilvercoppergoldsilvercopper
3502010502010502010
4108040610506010050
5408050101810100180100
61030801121180110210180
7109090123270120300270

kind regards,
Erik

erik.van.geit said:
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.

Using the initial data as Erik has proposed, here is my take on it ...
Book1
ABCDEFGHIJ
1goldsilvercoppergoldsilvercopper
2000000
3502010502010
410804061050
5408050101810
61030801121180
7109090123270
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!

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

erik.van.geit said:
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.

I meant "when the result is negative" then you get strange results
with mine and as well as with Yogis formulas
Map3.xls
DEFGHI
1goldsilvercoppergoldsilvercopper
2000000
3-10-10-10-118990

Replies
3
Views
246
Replies
2
Views
113
Replies
6
Views
503
Replies
25
Views
768
Replies
1
Views
2K

1,196,113
Messages
6,013,547
Members
441,770
Latest member
Griggsy28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back