Pounds and ounces

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
SvgmassiveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Owen RubinConsultantCommented:
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?
David Johnson, CD, MVPOwnerCommented:
Your sample file is 0 bytes reupload it. is it .04 lbs? or .04 oz?
Owen RubinConsultantCommented:
David, glad to see I was not the only one confused.   :-)
Determine the Perfect Price for Your IT Services

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

SvgmassiveAuthor Commented:
File attached
Book1.xlsx
Owen RubinConsultantCommented:
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.
SvgmassiveAuthor 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
David Johnson, CD, MVPOwnerCommented:
check this out
Owen RubinConsultantCommented:
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.
efrimpolCommented:
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.
efrimpolCommented:
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
Owen RubinConsultantCommented:
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.
David Johnson, CD, MVPOwnerCommented:
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 ?
SvgmassiveAuthor Commented:
I reached out to the individual,the entries needed to be corrected,I have uploaded a workbook
Book3.xlsx
efrimpolCommented:
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
Owen RubinConsultantCommented:
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
Owen RubinConsultantCommented:
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
Owen RubinConsultantCommented:
Here is a file with the solution (I hope)
Book4.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Owen RubinConsultantCommented:
One note: I assumed all the numbers were positive numbers. This will fail for negative numbers actually.
SvgmassiveAuthor Commented:
Wouldn't vba easier.Any vba examples?
Owen RubinConsultantCommented:
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.
Owen RubinConsultantCommented:
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
efrimpolCommented:
i have no issue relinquishing my points to Owen.
Owen RubinConsultantCommented:
Thank you. I just don't know how to close a question in the new interface as I have not done it yet.
SvgmassiveAuthor Commented:
Thanks every one
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.