Selcting best 8 weights and adding up in lbs and oz's

[glenws]

Hey hope someone can help

I have a very limited knowledge of macro and the developer section but here is what i am trying to achieve.

My dad runs a series of fishing matches thorughout the year and wants to create a leaderboard after each match.

It is a total of 12 matches with each anglers best eight weight cathces counting toward the total weight series.

I am trying to achieve something like below with the best 8 weight series calculated in lbs & oz

as an example 5.12 is 5lb 2oz

So i guess i need a formula or macro that selects the 8 highest weights then adds them up with an equation that every 16 oz = 1lb.

so for example currently if i added 5.12 + 51.02 + 23.04 excel will give a total of 79.18. adding these together in lb and ozs actually gives 80lb 2oz

Hope the above makes sense what i am trying to achieve and hope someone can help

Cheers Glen

 

[JB_Scotland]

B2 formula:

=LARGE(C2:N2,1)+LARGE(C2:N2,2)+LARGE(C2:N2,3)+LARGE(C2:N2,4)+LARGE(C2:N2,5)+LARGE(C2:N2,6)+LARGE(C2:N2,7)+LARGE(C2:N2,8)

will give you total of 8 highest weights.

 

[Andrew Poulsom]

Not an answer to your question but are you sure that 5.12 is 5lb 2oz and not 5lb 12oz? I notice that the highest decimal value in your table is .15 and there are 16 ounces in a pound.

Can you please post your actual data rather than an image so we can copy/paste rather than typing it.

 

[Matty]

B2 formula:

=LARGE(C2:N2,1)+LARGE(C2:N2,2)+LARGE(C2:N2,3)+LARGE(C2:N2,4)+LARGE(C2:N2,5)+LARGE(C2:N2,6)+LARGE(C2:N2,7)+LARGE(C2:N2,8)

will give you total of 8 highest weights.

Another (shorter) way of writing this would be:

=SUM(LARGE(C2:N2,ROW(INDIRECT("1:8"))))

Array entered, i.e. with CTRL+SHIFT+ENTER.

As per Andrew's point though, it's important we understand how the values are written in order to provide a workable solution.

Matty

 

[clabulis]

Untested (and ugly), but enter it with ctrl +shift +enter :

=(QUOTIENT(SUM(100*(LARGE(C2:N2,ROW(1:8))-INT(LARGE(C2:N2,ROW(1:8))))) ,16)+SUM(INT(LARGE(C2:N2,ROW(1:8)))))&" lbs. "&MOD(SUM(100*(LARGE(C2:N2,ROW(1:8))-INT(LARGE(C2:N2,ROW(1:8))))),16)&"oz."

 

[JB_Scotland]

Thanks Matty,

Can you explain the workings of INDIRECT("1:8") within the array formula? I can't quite get my head round it.

John

 

[Big Wade]

He's storing the ounces as the decimal , therefore 5.12 would be 5 lb 12 oz.

The quickest way without writing a macro would be to convert to ounces in the row below

Populate C3 - N3 with this formula:
=(INT(C2)*16)+((C2-INT(C2))*100)

Then write in another cell:
=(sumproduct(large(C3:N3,row(1:8)))) / 16

The answer will be in lb's.

 

[Andrew Poulsom]

Formula in B2 copied down:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Best 8	Match1	Match2	Match3	Match4	Match5	Match6	Match7	Match8	Match9	Match10	Match11	Match12
2	Angler1	120.06	5.12	51.02	23.04	1.08	0	14.12	13.08	4.08	3.12	3.12	0	1.08
3	Angler2	103.1	4.12	12.09	23.15	6.08	9.01	3.08	22.07	9.05	0	0	4.02	15.01
4	Angler3	80.02	5.12	51.02	23.04	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
B2	=DOLLARFR(SUMPRODUCT(DOLLARDE(LARGE(C2:N2,ROW(INDIRECT("1:8"))),16)),16)

 

[Matty]

Thanks Matty,

Can you explain the workings of INDIRECT("1:8") within the array formula? I can't quite get my head round it.

John

In fact, the INDIRECT call is superfluous. This will also work for picking out the 8 largest values:

=SUM(LARGE(C2:N2,ROW(1:8)))

Again, array entered.

That's not to say this solution will deliver what the OP needs, however.

Matty

 

[glenws]

Thanks for the quick response guys. Andrew this formula will save my dad so much time!! Thanks again