# Thread: Pounds and Ounces subtraction formula Thanks: 0 Likes: 0

1. ## Pounds and Ounces subtraction formula

Hi all

Through column B I have a simple list of numbers which represent pounds and ounces

B5 = 144.2 (so this represents 144 pounds and 2 ounces)
B6 = 138.9 (so this represents 138 pounds and 9 ounces)

what formula could I use so that C1 shows the difference between B5 and B6 (and shown in lbs/ouz)

example for above B5-B6, C1 would show answer as 5.9 , ie 5 pounds 9 ounces

TIA

3. ## Re: Pounds and Ounces subtraction formula

This is a bit "schoolboy" but does the trick:
In C1:

=QUOTIENT((LEFT(MAX(B5,B6),FIND(".",MAX(B5,B6))-1)*16)+RIGHT(MAX(B5,B6),LEN(MAX(B5,B6))-FIND(".",MAX(B5,B6)))-((LEFT(MIN(B5,B6),FIND(".",MIN(B5,B6))-1)*16)+RIGHT(MIN(B5,B6),LEN(MIN(B5,B6))-FIND(".",MIN(B5,B6)))),16)&"."&MOD((LEFT(MAX(B5,B6),FIND(".",MAX(B5,B6))-1)*16)+RIGHT(MAX(B5,B6),LEN(MAX(B5,B6))-FIND(".",MAX(B5,B6)))-((LEFT(MIN(B5,B6),FIND(".",MIN(B5,B6))-1)*16)+RIGHT(MIN(B5,B6),LEN(MIN(B5,B6))-FIND(".",MIN(B5,B6)))),16)

I'm sure there's a neater way of doing it, though ...

4. ## Re: Pounds and Ounces subtraction formula

Try one of the following

Excel 2010
BC
15.9
2895.9
35.9
4
5144.22306
6138.92217
789

2a

Worksheet Formulas
CellFormula
B2=(INT(B5)*16+MOD(B5,1)*10)-(INT(B6)*16+MOD(B6,1)*10)
C1=INT(B5)-INT(B6)-(MOD(B5,1)<MOD(B6,1))+((MOD(B5,1)<MOD(B6,1))*16+MOD(B5,1)*10-MOD(B6,1)*10)/10
C2=INT(B2/16)+MOD(B2,16)/10
C3=INT(((INT(B5)*16+MOD(B5,1)*10)-(INT(B6)*16+MOD(B6,1)*10))/16)+MOD(((INT(B5)*16+MOD(B5,1)*10)-(INT(B6)*16+MOD(B6,1)*10)),16)/10
C5=INT(B5)*16+MOD(B5,1)*10

5. ## Re: Pounds and Ounces subtraction formula

Im curious how you deal with 10 ounce weights.

As numbers, 3.1 = 3.10.
So, when you convert these numbers to weights, does 3.1 represent 3 pounds 1 ounce or 3 pounds 10 ounces.

(If Excel "sees" 3.1 is as text rather than numbers, this issue goes away. But others arise. Similarly, if you require the ounces to be expressed with two deicmal places (3 pounds 1 ounce being represented by 3.01) it also goes away.)