Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excell- format cell to pounds ounces

Posted on 2000-03-14
Medium Priority
774 Views
I maintain a spreadsheet for a fishing club and need to format the TOTAL column to reflect a cumulative total of pounds and ounces.  Just using sum doesn't give a correct answer.  Any ideas??
0
Question by:Teejay
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 7
• 3
• +2

LVL 22

Expert Comment

ID: 2615935
Teejay,

A couple of questions:
How many ounces are there in a pound?
How are your values entered? Is 2 pounds, 3 ounces entered as 1.3?

Ture Magnusson
0

LVL 5

Expert Comment

ID: 2616038

Assuming your Pounds are in Column A - A2:A6.

Assuming your Ounces are in Column B - B2:B6.

Enter the following formula wherever you like:

=ROUND((SUM(A2:A6)*16+SUM(B2:B6))/16,0) & " Pounds " & MOD((SUM(A2:A6)*16+SUM(B2:B6)),16) & " Ounces"

Dave
0

LVL 17

Expert Comment

ID: 2616215
Hi Teejay (and others)

Assuming you have the formatting Tigerman proposes (pounds & ounces in separate columns), TigerMan's formula will be correct if you slightly change the formula (INT instead of ROUND). The round function will round to the nearest integer, INT will take the integer. The second part (the mod function) takes over the non-divisible-by-16 part of the total calculated.

=INR((SUM(A2:A6)*16+SUM(B2:B6))/16) & " Pounds " & MOD((SUM(A2:A6)*16+SUM(B2:B6)),16) & " Ounces"

That is, of course, if you don't use the notation Ture is mentioning, 4,5 or 4,12 for respÃªctively 4 pounds 5 ounces & 4 pounds 12 ounces.

Ture, FYI, 16 ounces in one Pound.

Cheers

Calacuccua
0

LVL 17

Expert Comment

ID: 2616444
Typing Error:

that should be INT of course:

=INT((SUM(A2:A6)*16+SUM(B2:B6))/16) & " Pounds " & MOD((SUM(A2:A6)*16+SUM(B2:B6)),16)
& " Ounces"
0

LVL 5

Expert Comment

ID: 2617291
calaccucia,

Oops, meant:

=ROUNDDOWN((SUM(A2:A6)*16+SUM(B2:B6))/16,0) & " Pounds " & MOD((SUM(A2:A6)*16+SUM(B2:B6)),16) & " Ounces"

Thank you.

Dave
0

Author Comment

ID: 2620627
Sorry guys, I should have been more specific in my question. Both pounds and ounces are entered in the same cell.  2lb 4oz would be 2.04 and 2lb 14 oz would be 2.14.
The sheet is set up as follows:
Contestant
Name         Jan   Feb   Mar etc  Totall
John Doe   2.14  2.04                 5.03

The sheet is set up this way so that I can convert it as is to HTML and post it to our club web page.  Hope this clarifys.

0

LVL 13

Expert Comment

ID: 2620937
Assuming you want to convert value in cell A1:
=(INT(A1)*16+(A1-INT(A1))*100)*0.0283495
0

LVL 17

Expert Comment

ID: 2621693
Hi Teejay,

supposing your data is in first ten columns, put this formula in total column as an array formula (press Shift+Ctrl+Enter after you typed or pasted the formula)

=INT(SUM(INT(A1:J1)*16+(A1:J1-INT(A1:J1))*100)/16)+MOD(SUM(INT(A1:J1)*16+(A1:J1-INT(A1:J1))*100),16)/100

For only two columns, A and B, it will look like:

=INT(SUM(INT(A1:B1)*16+(A1:B1-INT(A1:B1))*100)/16)+MOD(SUM(INT(A1:B1)*16+(A1:B1-INT(A1:B1))*100),16)/100

To see if the array formula was accepted, after having pressed Ctrl+Shift+Enter, it should be surrounded by parentheses "{" and "}", like this

{=INT(SUM(INT(A1:J1)*16+(A1:J1-INT(A1:J1))*100)/16)+MOD(SUM(INT(A1:J1)*16+(A1:J1-INT(A1:J1))*100),16)/100}

The range A1:J1 should be changed in the complete formula at your convenience.
Once entered succesfully at the first line, the formula can be copied down as standard formula's.

Once again, type the formula, and press Ctrl+Shift+Enter together to validate it, even after every modification you make in it.

Hope this helps

Calacuccia
0

LVL 13

Expert Comment

ID: 2622138
Oops, sorry.
0

LVL 5

Expert Comment

ID: 2623048
calacuccia,

This is a highly interesting problem.

Assuming that your formulae are the same - except for the range values, neither of them work in this situation.

Teejay has stated that each cell in the range of data contains the following format:

2.14      equivalent to 2 pounds 14 ounces

2.04      equivalent to 2 pounds 4 ounces

Let's talk about these as the Left (referring to the 2 in both cases) and the right (14 and 04).

I will here have a go at pseudocode solution.
The problem then consists of:

Sum the Right minus Int(Sum(Right)/16) * 16 and insert "ounces"

I have worked up a partial solution [array formula on data in only A2, A3, A4]:

=SUM(VALUE(LEFT(A2:A4,SEARCH(".",A2:A4,1)))) + ROUNDDOWN(SUM(VALUE(RIGHT(A2:A4,SEARCH(".",A2:A4,1))))/16,0)&" Pounds "&MOD(SUM(VALUE(RIGHT(A2:A4,SEARCH(".",A2:A4,1)))),16)&" Ounces"

It tests and runs OK -   UNTIL:either:

The Left = 0  AND the right = 0

The Right has only a single digit i.e. 1        this would be the case if Teejay were to enter 2.10 - autoformat reduces it to 2.1.

Referring to the pseudocode above, the single Right digit problem is defined as:

A Right = 1 should actually be 10

There are other boundaries that give rise to error with my formula also - but we are then getting quite complex and trebling the function size with conditional and IsError calls.

I have subsequently tinkered with Fixed and a bunch of other little tricks - no luck.

Can you improve on the above formula?  Or anyone?

Dave
0

LVL 17

Expert Comment

ID: 2623373
This is for Dave (aka TigerMan), Teejay, I checked and verified my formula and it should work in all cases...

Dave,

You are separating both parts of the comma-separated number by using

VALUE(LEFT(A2:A4,SEARCH(".",A2:A4,1))) for the left part, and:

VALUE(RIGHT(A2:A4,SEARCH(".",A2:A4,1))) for the right part.

For the left part, this formula will work correctly provided, the number contains a comma, which is not the case for integers (e.g. 5), so formula doesn't work.

For the right part, the formula is wrongly conceived. Syntax for right:
Right(String,Length) will return the Lenght number of rightmost characters in String.
You calculate the Length as Lenght=Search(".",String,1) which means: Search will retun the number of the character in "String" at which the character "." is found, READING from LEFT TO RIGHT. So, you were lucky to get the thing working of numbers as 1,05 as you need two digits and by accident the "." is found at the 2nd character from the left. Imagine a number 15435434,01 where you also need two digits, well the search formula as conceived will return 9, so you're right part will be 9 digits long.

You could use the search function in combination with LEN function, which returns the number of characters in a string:

Length = Len(String) - Search(".",String,1)

In the example of 1,05 this will return 2, and in the example of 15435434,01 this will return (11-9) = 2 so works correctly.

A third problem, as you stated is in the case of e.g. 5,1 where you only need one digit in your right string, which obviously is not returned by the original function for search. The Len(String)-Search(".",String,1) would return 1 digit, but even that doesn't solve the problem, as you will remain with a value of 1, where you should have had 10 (2 digits needed, but only one present...!)

I think the best way is to recalculate verything to ounces, (see my formula) by multiplying left by 16, and taking (NUMBER-INT(NUMBER))*100 to transform the number of ounces correctly, and retransform the sum into the old notation. The string search functions are great in most cases, but you really have to be sure about the format you will have to deal witH.

Cheers

CalaCuCCia
0

LVL 5

Expert Comment

ID: 2623439
calacuccia,

I would like to point out that my reader presents the delimiter as a stop i.e. as in:

Name         Jan   Feb   Mar etc  Totall
John Doe   2.14  2.04                 5.03

not a comma.

Therefore I am not sure that your notes re commas is relevant.  Like I said, I will have a look at your comments in more detail momentarily.

Dave
0

LVL 5

Expert Comment

ID: 2623538
calacuccia,

Your formula, modified for a different range, and as entered as an array formula in my worksheet is (straight copy/paste):

=INT(SUM(INT(J2:L2*16+(J2:L2-INT(J2:L2))*100)/16)+MOD(SUM(INT(J2:L2)*16+(J2:L2-INT(J2:L2))*100),16)/100)

This from your original formula for comparison purposes:

=INT(SUM(INT(J2:L2)*16+(J2:L2-INT(J2:L2))*100)/16)+MOD(SUM(INT(J2:L2)*16+(J2:L2-INT(J2:L2))*100),16)/100

Placing the following values:

J2 0.05   for 5 ounces
K2 0.05   for 5 ounces
L2 0.15   for 15 ounces

the formula returns a solitary 1

The result should be something like 1 9 for 1 pound 9 ounces.

Copying down to the next row, with the values:

J3 1.05   for 1 pound 5 ounces
K3 0.08   for 8 ounces
L3 0.10   for 10 ounces

again returns a solitary 2

The result here should be something like 2 7 for 2 pounds 7 ounces.

By this example the formula appears flawed since Teejay is seeking a result like 1.09 and 2.07 respectively.

Is there any substance to our differences over . or ,   ?

I have read and digested your comments.  Beleive it or not, I understand what you are saying, and have learned from this interaction.

Thank you.

Dave

0

LVL 17

Expert Comment

ID: 2623599
Dave,

from your example with 0,05 - 0,05 and 0,15, my formula returns 1,09 in my version.

It seems to me, that in the cell where you place the formula, you have to show two digits behand the comma (or point). The number is rounded to integers on your spreadsheet, I would presume. 1,09 becomes 1 and 2,07 becomes 2. Logical.

I've noticed the "," and "." difference, but that doesn't make a difference to my comments.

Cheers

Calacuccia
0

LVL 5

Expert Comment

ID: 2625331
calacuccia,

You wrote:
It seems to me, that in the cell where you place the formula, you have to show two digits behand the comma (or point). The number is rounded to integers on your spreadsheet, I would presume. 1,09 becomes 1 and 2,07 becomes 2.

Sorry, but I did check that to 12 significant digits before I first replied.  At present it stands at 2.0000.  I realise that this seems to be somewhat antagonistic - that is not my intent.  The thing just simply doesn't work - could it be machine dependent in some way?  For example, and back to the , or . difference, that suggests you are working on a PC in Europe where in some places the , and . are synonymous.  Maybe this impacts on the functionality and gives rist to differences?

Feel free to depart the public light and reply to d64471023a@hotmail.com

Teejay, sorry if all this sort of off-topic stuff has been a hindrance to you, and thank you for your patience.

It would be really interesting to get your feedback on the functionality of the solution provided.

Dave

0

Author Comment

ID: 2628803
Thanks for your input guys, but your out of my league with these formulas. Please bear with me while I try them out. So far I am not getting the desired result.  I will work on them over the weekend. Thanks.
0

LVL 17

Expert Comment

ID: 2629262
Teejay,

<Bearing with you, in a few hours that should read 'BEERING' with you>, Have you tried my original formula (just copy the simple one), and have you hit Ctrl+Shift+Enter after having entered it.
If yes, did you encounter the same behaviour as TigerMan describes. If you haven't been able to test it yet, don't bother, take your time and report later. As Dave (aka TigerMan) stated, your feedback will be really ineresting.

For testing purposes, I re-paste the formula in its simplest version, with the original values in cells A1 and B1 (as in your example)
=INT(SUM(INT(A1:B1)*16+(A1:B1-INT(A1:B1))*100)/16)+MOD(SUM(INT(A1:B1)*16+(A1:B1-INT(A1:B1))*100),16)/100

Dave,

I don't know how the "," or "." separator could play a role in my formula. Either setting will recognise the Int formula as being the part left to the separator.

Calacuccia
0

LVL 17

Accepted Solution

calacuccia earned 400 total points
ID: 2629283
Dave (and Teejay),

I've found what your problem is. You have showed it yourself in fact in one of your previous comments.

You wrote:

>calacuccia,

>Your formula, modified for a different range, and as entered as an array formula in my worksheet is (straight copy/paste):

>=INT(SUM(INT(J2:L2*16+(J2:L2-INT(J2:L2))*100)/16)+MOD(SUM(INT(J2:L2)*16+(J2:L2-INT(J2:L2))*100),16)/100)

>This from your original formula for comparison purposes:

>=INT(SUM(INT(J2:L2)*16+(J2:L2-INT(J2:L2))*100)/16)+MOD(SUM(INT(J2:L2)*16+(J2:L2-INT(J2:L2))*100),16)/100

If you look good, there's a big difference. The second one of the two formula's starts with
=INT(SUM(INT(J2:L2)*16+../100 (the one I use)
where the one you tested is written as:
=INT(SUM(INT(J2:L2*16+../100)

The closing parenthese after L2 is in your case after the final division by 100, which makes the formula can only return integer, as it can be reduced to
=INT(AnyNumber)

For better comprehension, a decomposition of my formula:

IN the formula, you'll find INT(J2:L2)*16+(J2:L2-INT(J2:L2))*100 a couple of times. This can be read as the
'The integral part of number J2' multiplied with 16 (to convert pounds to ounces) +
('J2'-'The integral part of J2')*100 (or the ounces)
This gives as a result the total number of ounces, and is represented by OUNCES(J2) in the simplified formula beneath:

=INT(SUM(OUNCES(J2:L2))/16)+
MOD(SUM(OUNCES(J2:L2)),16)/100

The integral part of the total (summed) number of Ounces divides by 16 (to re-convert into pounds) + the Modulus 16 of the Ounces, divided by 100, to get them on the right part of the number.

Cheers

Calacuccia
0

LVL 5

Expert Comment

ID: 2630181
calacuccia,

Your last response correctly identifies the problem, and the original formula has been implemented and works fine.  Well done.

As to the cause?  I have absolutely no idea - clearly I simple grabbed a copy and shoved it in the hole on my sheet - the only thing I can think of is maybe during the change from A:J range to J:K:L range I have made an error, Excel has provided an auto fix that looked right, and I accepted it.  Really don't know.

In addition, I admit examining closely your original formula, testing parts of it - each one consistently worked OK.  I just didn't get to put yours and mine down one under the other, and make a close comparison.

Sorry for causing you all that grief.

I don't know if this has any beering on you current situation, but thanks for your patience.

Dave
0

Author Comment

ID: 2640858
I had no idea my question would result in such a complex discussion and analysis, but I certainly found it to  be educational.  My sincerce thanks to both Calacuccia who came up with the correct answer and also to TigerMan for his analysis.
0

LVL 17

Expert Comment

ID: 2640871
It was educational to me too.

Cheers

Calacuccia
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.