Calculating the highest six values into lbs and oz metric

[glenws]

SAMPLE.png

Hi All

above is an attached image showing my spreadsheet of fishing match results.

Basically throughout the season we take the bes six weights from a series of matches which goes o an overall series competition

I have done conditional formatting (cells highlighted blue) which identifies the six highest weights of each angler.

All i am now trying to do is calculate these six values into column D (Series Total) but add them in terms of Lbs and Oz (16 oz to lb)

I dont mind the cell not having Lb and ozs after the weight but just need to find a way of adding in terms of them so for example 20.06 + 30.14 would be 51.04 (51lb 4oz) and not 50.20

Is this possible?

Many Thanks
Glen

[jason.b75]

Adjust ranges to suit, note that the formula must be array confirmed using shift ctrl enter.

=--SUBSTITUTE(SUBSTITUTE(TEXT(SUM(IF(ISNUMBER($E2:$R2),IF($E2:$R2>=LARGE($E2:$R2,6),--(SUBSTITUTE($E2:$R2,"."," ")&"/16")))),"# 00/16"),"/16","")," ",".")

[glenws]

Thanks for this great formula. Will save me hours of manual calculating and converting. great stuff!!

I have added it into Column D and works a treat for 90% of cases.

Were there is a weight for example 10.00, 19.00, 21.00 (.00 ounces) it doesn't seem to want to calculate.

Is there a way around this. If i type 10.000000000000000001 for example it seems to sort this but if there was another way within the formula that would be fantastic.

[glenws]

Thanks for this great formula. Will save me hours of manual calculating and converting. great stuff!!

I have added it into Column D and works a treat for 90% of cases.

Were there is a weight for example 10.00, 19.00, 21.00 (.00 ounces) it doesn't seem to want to calculate.

Is there a way around this. If i type 10.000000000000000001 for example it seems to sort this but if there was another way within the formula that would be fantastic.

[ChemistB]

Here's another array function. I didn't try to figure out your method of pulling the top 6. In my formula, they'd already be pulled (into another table or sheet). I used an example of top 3 (being in A4:C4)

Here's the formula
=SUM(INT(SUM(MOD($A$4:$C$4,1))*100/16),INT($A$4:$C$4))& " lbs, "&ROUND(MOD(SUM(MOD($A$4:$C$4,1))*100/16,1)*16,0)&" ozs" entered as an array
See attachment

[ChemistB]

I updated my attachment with a new tab where I incorporated some of Jason's formula. The new formula is

=SUM(INT(SUM(MOD(IF($E2:$R2>=LARGE($E2:$R2,6),$E2:$R2),1))*100/16),INT(IF($E2:$R2>=LARGE($E2:$R2,6),$E2:$R2)))& " lbs, "&ROUND(MOD(SUM(MOD(IF($E2:$R2>=LARGE($E2:$R2,6),$E2:$R2),1))*100/16,1)*16,0)&" ozs"

Now that's a whopper.

[daddylonglegs]

So on your example line 2 should be a total of 176.12? Assuming you will always have at least 6 values in the row you could try this "regular" formula [revised]

=DOLLARFR(SUM(LARGE(E2:R2,{1,2,3,4,5,6})+MOD(LARGE(E2:R2,{1,2,3,4,5,6}),1)*21/4),16)

That uses DOLLARFR function which requires Analysis ToolPak add-in to be enabled if you are using Excel 2003 (Tools > add-ins > tick the "Analysis ToolPak" box). That formula will only ever sum six values, even where there are ties.

This version will add all the values if there are 6 or less

=DOLLARFR(SUMPRODUCT(LARGE(E2:R2,ROW(INDIRECT("1:"&MIN(6,COUNT(E2:R2)))))+ MOD(LARGE(E2:R2,ROW(INDIRECT("1:"&MIN(6,COUNT(E2:R2))))),1)*21/4),16)