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??
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.
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
With monday.comâs project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
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.
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)
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.
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.
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.
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.
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
TeejayAuthor Commented:
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.
<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.
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:
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.
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
TeejayAuthor Commented:
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.
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