Convert 1.16 (1lb 16oz) to read "2 lbs"

[EcoMike]

Hi folks,

Trying to create a sheet to tally up fish weights for a small fishing tournament. The weights would be entered in decimals and I would like the sheet to total up everyones weights and output a total weight that reads in "# lb # oz".

I have a test sheet going here where I'm trying to convert cell B34 cell value "1.16" (1lb 16oz) to read "2 lb" in cell B35.

Hope someone can help.

Thanks!

[shg]

	A	B	C
1	lb.oz	lb
2	1.00	1.000	B2: =DOLLARDE(A2, 16)
3	1.02	1.125
4	1.03	1.188
5	1.04	1.250
6	1.05	1.313
7	1.06	1.375
8	1.08	1.500
9	1.09	1.563
10	1.10	1.625
11	1.14	1.875
12	1.15	1.938
13	1.16	2.000

[EcoMike]

Hey shg! Thx for the reply.

Very clever. I got your formula to work in combination with another so that it converts the decimal into ounces. Here's an example...


A1 = 1.10
A2 = 1.10
A3 = "SUM (A1:A2)
A4 = DOLLARDE(A3, 16)
A5 =INT(A4)&"lb, "&ROUND((A4-INT(A4))*16,1)&"oz"

A5 is producing the correct data "3 lbs 4 oz"

My question to you is how would I do the same thing but but removing a step by combining A4 & A5 into one formula?

[shg]

How about posting an example workbook?

[63falcondude]

Substitution would give you this in A5:

=INT(DOLLARDE(A3,16))&"lb, "&ROUND((DOLLARDE(A3,16)-INT(DOLLARDE(A3,16)))*16,1)&"oz"

but you still have the formula in A3 which can be substituted into the A5 formula as well leaving us with this:

=INT(DOLLARDE(SUM(A1:A2),16))&"lb, "&ROUND((DOLLARDE(SUM(A1:A2),16)-INT(DOLLARDE(SUM(A1:A2),16)))*16,1)&"oz"

[Bo_Ry]

Plaese try
=SUBSTITUTE(TEXT(DOLLARFR(DOLLARDE(SUM(A1:A2),16),16)*100,"0 l\b 00 oz")," 0"," ")

[EcoMike]

Howdy peeps,

You guys are frickin geniuses. Bo_Ry... yours was the first one I tried and IT WORKED like a charm so I'll stick with this for now.

Would it too much to ask to help me fine tune this sheet even more? There's only one more thing I'd like the sheet to do...

Keep track of the category, "Most Walleye". This is a 4 day tournament and I would like this cell to output the name of the person who has the most walleye (1 pound or over) as each day progresses. I would be sure to enter fish weight values as "w.1.10" (walleye 1lb 10oz) OR whatever you suggest would be best.

Here's a link to the sheet. You CAN edit. All names are correct and final for this trip. If a few of you are entering formulas then enter your own under "eg. #"
Link:https://docs.google.com/spreadsheets...it?usp=sharing

Thank you so much, in advance.

Mike

[FlameRetired]

Mike you can upload direct to this forum. Many members are reluctant to download from 3rd party file sharing sites. Doing so reduces the number of willing participants.

If you are not familiar with how to do this:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[EcoMike]

Ahhh. Thx. See attached file.

Mike

[FlameRetired]

Mike are you sure this is the right file? It only has one weight listed in the source data.

[EcoMike]

Yup. This is an event that hasn't happened yet. However, let me save you all the time.. Here's a data populated sheet.

[FlameRetired]

Try putting this helper row in B8:L8

Please Login or Register  to view this content.

Then array enter this in B11 and fill down.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[davsth]

Whilst I can see the formula working in the example, if the number of catches was such that the sum of the oz was 100 or more then the formula would yield false results. so in theory 7 catches could produce erroneous results, but in the example you can only catch 5 fish!

Both the above formulas from bo and falcon work for me

[FlameRetired]

What happens if you adjust the ranges in the helper row?

If those solutions take care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[EcoMike]

Thanks FR.

I hate to be a bother when you all have been so helpful, but could I ask you too load your formulas into the sheet and re-post?

I tried what you said but my I cant stop my eyes from bleeding. It's probably because I'm an idiot, or because I'm using Google Sheets; the Ctrl-Shift Enter thing doesnt work in the browser. Nor do I know what a helper row is for; not do I know what your Array formula is trying to do.

[EcoMike]

Whilst I can see the formula working in the example, if the number of catches was such that the sum of the oz was 100 or more then the formula would yield false results. so in theory 7 catches could produce erroneous results, but in the example you can only catch 5 fish!

Both the above formulas from bo and falcon work for me

I didn't realize this. Thanks for the heads up!

[FlameRetired]

Thanks FR.

... could I ask you too load your formulas into the sheet and re-post?

... I'm using Google Sheets; the Ctrl-Shift Enter thing doesnt work in the browser. Nor do I know what a helper row is for; not do I know what your Array formula is trying to do.

I don't know about the limitations of Google Sheets. That may be part of the problem.

See the upload. The helper row is in row 8. It counts all the weights in each column >= 1.

The array formula adds a small amount to each of those helper cells to account for ties. This allows the LARGE function to distinguish among the ties. As you fill down the ROWS function returns the 1st, 2nd, 3rd etc largest values from the adjusted helper values. It then matches each of those to the same adjusted values in the helper row. That returns a column INDEXing number passed to INDEX of the names that returns the names.

That formula does not work by committing with just Enter. I must be committed by simultaneously holding down Ctrl + Shift while hitting Enter. {} will surround the formula in the formula bar. Excel does that for you. Do not type those in yourself or you will get an error.

[joeu2004]

As davsth notes, an expression of the form DOLLARFR(DOLLARDE(SUM(B2:B11),16),16) is wrong because, in general, SUM(B2:B11) is not the correct sum of values in the form n.m, where "m" represents m/16 (ounces) instead of m/100. Consider the following example:

Please Login or Register  to view this content.

The correct answer is 67.09.

But DOLLARFR(DOLLARDE(SUM(B2:B11),16),16) returns 62.05 because SUM(B2:B11) is 60.37.

Ideally, the following normally-entered formula (press Enter as usual) correcty results in "n lb m oz":

=SUBSTITUTE(TEXT(100*DOLLARFR(SUMPRODUCT(DOLLARDE(B2:B11,16)),16), "0 l\b 00 oz"), " 0", " ")

That does not work in Excel 2010(!). But you might try it in Google Sheets.

Alternatively, try the following array-entered formula (press ctrl+shift+Enter instead of just Enter):

=SUBSTITUTE(TEXT(100*DOLLARFR(SUM(DOLLARDE(B2:B11,16)),16), "0 l\b 00 oz"), " 0", " ")

Again, that does not work in Excel 2010(!). But you might try it in Google Sheets.

If you cannot use ctrl+shift+Enter in your browser(!), the following might work in Google Sheets, normally-entered (press Enter as usual):

=ARRAYFORMULA(SUBSTITUTE(TEXT(100*DOLLARFR(SUM(DOLLARDE(B2:B11,16)),16), "0 l\b 00 oz"), " 0", " "))

But that is just a wild guess. Please note that this is an Excel forum, not a Google Sheets forum. I rely on the following GS help page: https://support.google.com/docs/answer/3093275?hl=en .

Finally, the following normally-entered formula (press Enter as usual) does work in Excel 2010. Presumably, it works in Google Sheets as well.

=SUBSTITUTE(TEXT(100*DOLLARFR(SUMPRODUCT(INT(B2:B11))+SUMPRODUCT(ROUND(100*MOD(B2:B11,1),0))/16,16), "0 l\b 00 oz"), " 0", " ")

-----
PS.... To apply these formulas to your example Excel file, change B2:B11 to B2:B6, and enter (or array-enter) the formula into B7 and copy across.

[joeu2004]

try the following array-entered formula (press ctrl+shift+Enter instead of just Enter):
=SUBSTITUTE(TEXT(100*DOLLARFR(SUM(DOLLARDE(B2:B11,16)),16), "0 l\b 00 oz"), " 0", " ")
Again, that does not work in Excel 2010(!).

But mysteriously, the following array-entered formula (press ctrl+shift+Enter instead of just Enter) does work in Excel 2010:

=SUBSTITUTE(TEXT(100*DOLLARFR(SUM(DOLLARDE(N(B2:B11),16)),16), "0 l\b 00 oz"), " 0", " ")

[EDIT] Or more simply, the following normally-entered formula (just press Enter as usual):

=SUBSTITUTE(TEXT(100*DOLLARFR(SUMPRODUCT(DOLLARDE(N(B2:B11),16)),16), "0 l\b 00 oz"), " 0", " ")

YMMV using Google Sheets.

[Bo_Ry]

Whilst I can see the formula working in the example, if the number of catches was such that the sum of the oz was 100 or more then the formula would yield false results. so in theory 7 catches could produce erroneous results, but in the example you can only catch 5 fish!

I didn't realize this. Thanks for the heads up!

Fixed by
=SUBSTITUTE(TEXT(DOLLARFR(SUMPRODUCT(DOLLARDE(+B2:B6,16)),16)*100,"0 l\b 00 oz")," 0"," ")

[davsth]

If I put something that sums to an exact number in the above formula with lengthened ranges eg 16 1.15 it gives me 30lbs 16oz rather than 31 lbs 0 ozs

I suspect rounding errors, but the below works
=INT(SUMPRODUCT(RIGHT(B2:B17,2)+LEFT(B2:B17,LEN(B2)-3)*16)/16)&"lbs "&MOD(SUMPRODUCT(RIGHT(B2:B17,2)+LEFT(B2:B17,LEN(B2)-3)*16),16)&" ozs"