Pounds and Ounces subtraction formula

[redspanna]

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

 

[footoo]

https://answers.microsoft.com/en-us...nd-oz-in/1979e678-3d70-49e6-b921-00668255ab6e

 

[sykes]

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

 

[Dave Patton]

Try one of the following

Excel 2010
	B	C
1		5.9
2	89	5.9
3		5.9
4
5	144.2	2306
6	138.9	2217
7		89
2a

Cell Formulas
Range	Formula
B2	=(INT(B5)*16+MOD(B5,1)*10)-(INT(B6)*16+MOD(B6,1)*10)
C1	=INT(B5)-INT(B6)-(MOD(B5,1)B6,1))+((MOD(B5,1)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

 

[mikerickson]

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.)