Solved

Excell- format cell to pounds ounces

Posted on 2000-03-14
21
711 Views
Last Modified: 2010-08-05
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
Comment
Question by:Teejay
  • 8
  • 7
  • 3
  • +2
21 Comments
 
LVL 22

Expert Comment

by:ture
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
Karltad, Sweden
0
 
LVL 5

Expert Comment

by:TigerMan
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

by:calacuccia
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

by:calacuccia
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

by:TigerMan
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

by:Teejay
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

by:cri
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

by:calacuccia
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

by:cri
ID: 2622138
Oops, sorry.
0
 
LVL 5

Expert Comment

by:TigerMan
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:

Add the Left, add to this the Int(Sum(Right)/16) and insert "Pounds",
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 17

Expert Comment

by:calacuccia
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

by:TigerMan
ID: 2623439
calacuccia,

I will print off and examine your comments.

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

by:TigerMan
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

by:calacuccia
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

by:TigerMan
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

by:Teejay
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

by:calacuccia
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

by:
calacuccia earned 100 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

by:TigerMan
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

by:Teejay
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

by:calacuccia
ID: 2640871
It was educational to me too.

Cheers

Calacuccia
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now