# Pounds and Ounces subtraction formula

#### redspanna

##### Well-known Member
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

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### sykes

##### Well-known Member
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 ...

Last edited:

#### Dave Patton

##### Well-known Member
Try one of the following

#### mikerickson

##### MrExcel MVP
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.)

Last edited:

1,101,949
Messages
5,483,840
Members
407,415
Latest member
Anton1999

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...