This ought to be a good BRAIN BENDER

[1fishy]

I am trying to build a spreadsheet to total five separate fish weighe
in pounds and ounces. I need a column for the name of the fisherman
1st fish, 2nd fish, 3rd fish, 4th fish, 5th fish (each in pounds an
ounces), and a total column in pounds and ounces.
I would also like to designate a point value by the most weight t
least, (160pts 1st, 150pts 2nd ... 10 pts 16th place). I also need
column to denote a 2 oz penalty for each dead fish, that would then b
subtracted from the total. I also will need to be able to total betwee
two sheets, so I can show totals for a two day tournament where the 1s
and 2nd day and combined totals are on the same sheet. In this case th
point designation would not have to be figured for the 1st day, onl
for the two day total.

I have been pulling my hair out on this one! I am by no mean
experienced with Excel but I'm not lame either. Please don't assume
know anything though. I have been to the Microsoft site and downloade
the necessary addins. I have the formula for converting ozm > lbm
(=INT(CONVERT(C7,"ozm","lbm"))&" lbs " &INT
MOD(CONVERT(C7,"ozm","ozm"),16))& " oz") but am not sure how to ad
these up properly. Any help will be greatly appreciated!!! Thanks i
advanceI am trying to build a spreadsheet to total five separate fis
weighed in pounds and ounces. I need a column for the name of th
fisherman, 1st fish, 2nd fish, 3rd fish, 4th fish, 5th fish (each i
pounds and ounces), and a total column in pounds and ounces.
I would also like to designate a point value by the most weight t
least, (160pts 1st, 150pts 2nd ... 10 pts 16th place). I also need
column to denote a 2 oz penalty for each dead fish, that would then b
subtracted from the total. I also will need to be able to total betwee
two sheets, so I can show totals for a two day tournament where the 1s
and 2nd day and combined totals are on the same sheet. In this case th
point designation would not have to be figured for the 1st day, onl
for the two day total.

I have been pulling my hair out on this one! I am by no mean
experienced with Excel but I'm not lame either. Please don't assume
know anything though. I have been to the Microsoft site and downloade
the necessary addins. I have the formula for converting ozm > lbm
(=INT(CONVERT(C7,"ozm","lbm"))&" lbs " &INT
MOD(CONVERT(C7,"ozm","ozm"),16))& " oz") but am not sure how to ad
these up properly. Any help will be greatly appreciated!!! Thanks i
advanc

 

[Arvi Laanemets]

Hi

1st sheet (p.e. Day1)
Name, W1, W2, W3, W4, W5, WasDead, TotalW, Pts
Into cell H2 (TotalW) enter the formula
=IF(A2="","",SUM(B2:F2)-2*G2)
Into cell I2 (Pts) enter the formula
=IF(A2="","",CHOOSE(RANK($H2,$H$2:$H$17),160,150,140,130,120,110,100,90,80,7
0,60,50,40,30,20,10))
Copy both formulas down to row 17
Into column Name enter names of course
Copy your sheet into same workbook (right-click onto sheet tab, select 'Move
or copy', check 'Create a copy', and then OK, and then rename the sheet p.e.
Day2)
On sheet Day2, you can replace name in cell A2 with formula
=IF(Day1!A2="","",Day1!A2)
and copy it down to cell A17.

Into columns on both sheets Day1 and Day2 W1-W5 enter weights in pounds OR
in ounces (I'm practically ignorant about pounds and ounces, but I have a
feeling you cant use cell formatting here, and when you want enter pounds
AND ounces, then instead every W# you must have 3 columns, Pnd#, Oz#, and
W#, where in W# is calculated weight in selected units. Your table will be
cumbersome to handle.)
Into column WasDead enter the number of dead fishes (whatever you mean by
it)

On 3rd sheet, p.e. Summary, create a table
Name, TotalW, Pts
Into cell B2 (TotalW) enter the formula
=IF(A2="","",Day1!H2+Day2!H2)
Into Cell C2 enter the formula
=IF(A2="","",CHOOSE(RANK($B2,$B$2:$B$17),160,150,140,130,120,110,100,90,80,7
0,60,50,40,30,20,10))
Copy names from sheet Day1 (or sheet Day2) into first column.

The only weak point here is when there will be a draw between 2 contestants.
P.e. when 2nd and 3rd get same total weight of fish, then both of them get
140 pts. I'd prefer to give them (140+130)/2=135 pts to each, but until you
don't calculate summary points as sum of dayly points, it doesn't matter at
all.

 

[AlfD]

Hi!

The safe way to handle pounds (lb) and ounces (oz) is in separat
columns. You can then add the lb columns and the oz columns. A quic
sum at the end then moves suplus oz into the lb column ("carry figures
we used to call them).

So: add 3lb 5oz; 5lb 12oz; 4lb 11oz
Gives 12lb 28oz
That is 13lb 12oz.

In formula terms:
put lb and oz alternate in columns B,...K (5 fishes).
Put lb total in L1 as =sum (B1,D1,F1,H1,J1)
Put oz total in M1 as =sum(C1,E1,G1,I1,K1)
Put amended lb total in N1 as = L1+int(M1/16)
Put amended oz total in O1 as = mod(M1,16)
Hide columns L & M
If you want it to look traditional, put =N1 &"lb " & O1 &"oz" im P1
Hide cols N & O. P is your weight column, but for any arithmetic yo
have to depend on the numerical values in N & O.

Amazing that Excel can't just do a simple bit of hexadecimal arithmeti
on it! Even my calculator can.

Al

 

[1fishy]

I cannot thank you enough! I realize what I'm asking isn't for busines
or brain surgery. Just a little old fishing tournament weigh-in sheet
If it's any consolation, I will share this with many different club
having the same problems. Bass fishermen do not like to conver
decimals. Who knows it may be spread around nationally or eve
internationally.

I recently took an intermediate Excel class at work. The instructor
absolutely drew a blank with this. They didn't even know about thi
site??! I've learned more reading posts here than I did in class. Mos
of what they taught I figured out myself between making mistakes an
let's see what this button does.

I haven't had time to try any of this out yet. I hope I have time thi
week. I'll get back with you. Thanks again