Subtracting with a twist

[Guest]

Similar to a different post I put up (by the way, how do you delete a post after it's been answered???), I need to know how to SUBTRACT numbers in a specific way. The way my spreadsheet is currently setup, TL-3 and Tons-3 are determined by subtracting TL-2 from TL-1 and Tons-2 from Tons-1.

TL-1 Tons-1 TL-2 Tons-2 TL-3 Tons-
2 45 1 15 1 3
2 15 1 20 1 -

The problem, as you can see, is that if the Tons-2 is too high, Tons-3 is negative. I need to have Tons-3 calculate based on 22 Tons/TL. In other words, I need the spreadsheet to look like this

TL-1 Tons-1 TL-2 Tons-2 TL-3 Tons-
2 45 1 15 1 3
2 15 1 20 0 1

As you can see in the 2nd line, 15-20 took away all 15 with 5 left over. It then took 5 away from 22 = 17. Also, it subtracted one away from the TL-3 column. Any ideas for a calculation would be appreciated!

 

[bj]

one way would be to define
TL-3 as Int(((TL-1*22+Tons-1)-(TL-2*22+Tons-2))/22)
and
Tons-3 as Mod(((TL-1*22+Tons-1)-(TL-2*22+Tons-2)),22)

-----Original Message-----
Similar to a different post I put up (by the way, how do

you delete a post after it's been answered???), I need to
know how to SUBTRACT numbers in a specific way. The way
my spreadsheet is currently setup, TL-3 and Tons-3 are
determined by subtracting TL-2 from TL-1 and Tons-2 from
Tons-1.

TL-1 Tons-1 TL-2 Tons-2 TL-3 Tons-3
2 45 1 15 1 30
2 15 1 20 1 -5

The problem, as you can see, is that if the Tons-2 is too

high, Tons-3 is negative. I need to have Tons-3 calculate
based on 22 Tons/TL. In other words, I need the
spreadsheet to look like this:

TL-1 Tons-1 TL-2 Tons-2 TL-3 Tons-3
2 45 1 15 1 30
2 15 1 20 0 17

As you can see in the 2nd line, 15-20 took away all 15

with 5 left over. It then took 5 away from 22 = 17.
Also, it subtracted one away from the TL-3 column. Any
ideas for a calculation would be appreciated!