Pounds and Ounces subtraction formula

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,462
Office Version
2016
Platform
Windows
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
 

Some videos you may like

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
Joined
May 1, 2002
Messages
1,661
Office Version
365
Platform
Windows
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
Joined
Feb 15, 2002
Messages
4,088
Office Version
2010
Platform
Windows
Try one of the following

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">5.9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">89</td><td style="text-align: right;;">5.9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">5.9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">144.2</td><td style="text-align: right;;">2306</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">138.9</td><td style="text-align: right;;">2217</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">89</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=(<font color="Blue">INT(<font color="Red">B5</font>)*16+MOD(<font color="Red">B5,1</font>)*10</font>)-(<font color="Blue">INT(<font color="Red">B6</font>)*16+MOD(<font color="Red">B6,1</font>)*10</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=INT(<font color="Blue">B5</font>)-INT(<font color="Blue">B6</font>)-(<font color="Blue">MOD(<font color="Red">B5,1</font>)<MOD(<font color="Red">B6,1</font>)</font>)+(<font color="Blue">(<font color="Red">MOD(<font color="Green">B5,1</font>)<MOD(<font color="Green">B6,1</font>)</font>)*16+MOD(<font color="Red">B5,1</font>)*10-MOD(<font color="Red">B6,1</font>)*10</font>)/10</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=INT(<font color="Blue">B2/16</font>)+MOD(<font color="Blue">B2,16</font>)/10</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">(<font color="Green">INT(<font color="Purple">B5</font>)*16+MOD(<font color="Purple">B5,1</font>)*10</font>)-(<font color="Green">INT(<font color="Purple">B6</font>)*16+MOD(<font color="Purple">B6,1</font>)*10</font>)</font>)/16</font>)+MOD(<font color="Blue">(<font color="Red">(<font color="Green">INT(<font color="Purple">B5</font>)*16+MOD(<font color="Purple">B5,1</font>)*10</font>)-(<font color="Green">INT(<font color="Purple">B6</font>)*16+MOD(<font color="Purple">B6,1</font>)*10</font>)</font>),16</font>)/10</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">=INT(<font color="Blue">B5</font>)*16+MOD(<font color="Blue">B5,1</font>)*10</td></tr></tbody></table></td></tr></table><br />
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,618
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:

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top