Pounds and ounces

Svgmassive
Svgmassive used Ask the Experts™
on
I just imported data, and I am trying to get the total number of pounds and ounces. The problem some of the value are less than an ounces eg. 0.04 and some are ounces 0.4.I need a formula  that I can use to get the overall total pounds and ounces
Book1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
So is the number to the right of the decimal ounces? So 1.15 would be 1 lb 15 ounces? Adding 0.01 would give me 2.0?
Top Expert 2016

Commented:
Your sample file is 0 bytes reupload it. is it .04 lbs? or .04 oz?

Commented:
David, glad to see I was not the only one confused.   :-)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
File attached
Book1.xlsx

Commented:
Ok, so looking at the file I am even more confused.

So 0.1 is one ounce?  But 0.15 is 15 ounces? 0.4 is four ounces. So what would be 0.04?

Now I see why this is a problem.

Author

Commented:
two digits is less than an ounce that the way I received the data and it's driving me nuts.0.15 is less than an ounce,.0.04 is less than an ounce
Top Expert 2016

Commented:
check this out

Commented:
oh? I thought left of the decimal was pounds, right of the decimal was ounces In a strange left justified base 16 number.  No?

So 1.4 is 1 pound 4 ounces. But 1.04 is one pound, and something less than an ounce. But what something?
So 1.15 is 1 pound 15 ounces?
I  need some time, but I would first create a new column for ounces, then create a function to split the number to the right of the decimal into the new column.. Maybe the same for the left of then decimal number, so the results are two new columns with pounds in one column and the weird ounces in another.

Then you would need a function to determine ounces from partial ounces. To do that, I need to know the limits on that number? Only two numbers right of the decimal? If true, then anything less than 0.1 is a partial ounce. Anything above that is in ounces,and maybe a conversion table can give the change form wired ounces to real ounces.

Those functions I am not seeing in my head at 2:30 AM.  But this is a start.
1.15 is NOT 1 pound 15 ounces. 1 ounce equals 0.0625 pounds, 1.15 would equate to 1.0625 pounds, but 1 pound 15 ounces would equate to 1.9375 pounds.
Take a look at the following site for an example on pound to ounce conversion in Excel.

https://www.extendoffice.com/documents/excel/3056-excel-convert-pounds-to-ounces-grams-kg.html

Commented:
Oh sorry, as I said, it was not clear to me. Thanks. efrimpol's link might help.

So, if these are just pounds and fractions of pounds, can't you just add it all together? The whole number will be pounds. What is left is a percentage of pounds.  So all you need to do is separate the whole from the percentage.

Assume a cell which has the total of all numbers added together
Select a blank cell such as B2.
Enter the formula =INT(cell) into the formula bar where cell is the total cell, and then press the Enter key.
Now B2 will have the integer part, or "pounds" of the total.
Now select another cell, such as C2, and enter the formula =cell - INT(cell) * 16 where cell is the total cell
Now C2 will have ounces.
B2 has pounds. C2 has ounces.

(note you had comments in B, so on your file I used C and D instead)

Does that make sense?

If you are just trying to convert each entry into pounds and ounces, you can use this same formula set.
Just do the above for the first line in two cells to the right of each entry.
Then copy the B and C cell formulas down to all cells below in each column
Now columns B and C will have Pounds and ounces.

Here is your file converted: https://drive.google.com/open?id=1mVeqpdthoi_rZg8zbU9lRu5Rp_GEYt07

BUT...

Here is my confusion. Your file says this:
1.12       is 1lb,12 ozs
But above you said that is not what it means. Do you understand why I find this confusing?
I would think 1.12 is 1 pound, 1.92 ounces based on what you said in your comment above.

I can get this but need to be sure I understand what the numbers mean.
Top Expert 2016

Commented:
Yes it is confusing.  1.12 being 1 LB 12 or 1 LB and 12/16 of a LB
 .04 oz would be 4/16 of a pound or is 1.12 1 LB  12/100th of a LB .12 * 100 = 12 Oz ?

Author

Commented:
I reached out to the individual,the entries needed to be corrected,I have uploaded a workbook
Book3.xlsx
1.15 pounds

Using Owen's example and making the assumption that the values entered AFTER the decimal are ounces and not the true decimal representation of the ounces.

As you can see, adding the values as you see them is not the same as adding them as true pounds and ounces.
2018-10-16_174340.jpg

Commented:
Using Owen's example and making the assumption that the values entered AFTER the decimal are ounces and not the true decimal representation of the ounces.

Sadly, you are right, If they were decimal pounds, adding them is easy. Not, not so much now. BUT....

So this for 1.15 meaning 1 pound 15 ounces

Getting pounds is the same as before, the =INT(cell)
Getting ounces is a bit more complex. For single digits to the right of the decimal, you use a similar function as before, but multiply by 10:
=cell - INT(cell) * 10
BUT, if the value to the right of the decimal is 2 digits, you need to multiply by 100

I need to whip up a quick script for that, but I will have to do that tomorrow. My thinking is to do the  *10, check by subtracting the integer value of Column C from C itself, and if non-zero, multiple by 10 to move the full value into an integer.

Then, to add it all up, the sum of pounds is easy. Just add that column.
The sum of ounces will create a number that if you divide by 16,  the integer part will be pounds, you add that to the pound sum as that will be full pounds. I believe the remainder will be the number of total ounces as a fraction. Multiply that by 16 to get ounces as ounces! (I need to test to confirm.)

Again, I need to verify, but can't do it from my tablet.

More tomorrow

Commented:
OK, I got it.

The whole part of an entry is the pounds, and can be obtained like this:
=INT(cell)

The number of ounces was a bit harder to extract because it can be either one or two digits. That took some time to figure out. But to get a whole number of ounces, use this:
=MOD(cell,1)*10^(LEN(RIGHT(cell,LEN(cell)-FIND(".",cell))))
This will extract the number of ounces as a whole number (ex: 1.12) will return 12 for ounces.
(This line takes the number to the right of the decimal, counts the number of digits, and moves the decimal either one or two places to get a whole number)

Now you can add each column up to get total pounds and total ounces.
Take total ounces and divide by 16, to get number of pounds from those ounces, and add that to total pounds.
Multiply the decimal remainder of the total ounces by 16 to get total ounces not counted as pounds.

I will upload a sheet with the solution in the next answer
Commented:
Here is a file with the solution (I hope)
Book4.xlsx

Commented:
One note: I assumed all the numbers were positive numbers. This will fail for negative numbers actually.

Author

Commented:
Wouldn't vba easier.Any vba examples?

Commented:
I did not need VBA, it can all be done with Excel functions.  And it works!   And I suspect that functions are faster than VBA.
But if you wanted VBA, you should have asked for that right up front, and it would have saved me a lot of work. Just saying...

Just copy/paste the columns for all entries, and it works. If you need it to handle negative numbers, I can update for that. I just makes the function longer.

Commented:
Svgmassive: Can you please end this topic by picking a solution (like the one I gave you), and mark "helpful" any other posts that helped get you an answer please.

I spent a lot of time making that work. Be nice to get the points for it

Thanks
i have no issue relinquishing my points to Owen.

Commented:
Thank you. I just don't know how to close a question in the new interface as I have not done it yet.

Author

Commented:
Thanks every one

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial