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?
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?