Excel Formula's

[ketchumbass]

I am trying to create a complex formula and not sure how to do it. Hoping
that someone here can help. I have 2 spreadsheets, one's called "Angler of
the year" and the other is called "CLinton Lake". On the Clinton lake sheet,
I have the names of all members in the club. Each member could end up in
either 1st,2nd,3rd,4th,5th,6th,7th,8th place and so on. For each place there
is a point value. 1st is gonna be worth 300 points, 2nd is gonna be 250
points, 3rd is gonna be 225 points and so on. On the "angler of the year
sheet", each cell in one column is gonna need this formula. So if, John Brown
takes 1st place then he get's 300 points or if he takes 3rd place then he
gets 225 and so on for each memeber. Can anyone help me. If you need more
info on what I am trying to do then please let me know.

Thanks

 

[Gary Keramidas]

if i understand correctly, one way would be to use the choose formula.

for example, name in column A, place in column B, and this formula in column C:

=CHOOSE(B1,300,250,225)

just add the remaining values to the formula

--




Gary Keramidas
Excel 2003

 

[T. Valko]

I'm assuming that the angler's place is based on a total weight of catch?

So, what you'd need to do is rank each angler based on total weight then
you'd have a table that lists the rank and its corresponding point value.

A lookup formula would then be used. This is relatively simple until you get
ties! Is the total point pool limited to a specific amount? For example, in
a tournament the total "purse" is 1000 points. Those 1000 points then get
distributed to the participants based on their finishing rank.

1st = 500
2nd = 250
3rd = 175
4th = 75

2 people tie for 1st place:

Joe = 20
Sue = 20
Tom = 18
Liz = 15

So, the two 1st place finishers split the 1st and 2nd place points:

Joe = 375
Sue = 375
Tom = 175
Liz = 75

Is this the type of thing you're looking for?

 

[ketchumbass]

Yes it is...But I am using 2 different spreadsheets The 1st spreadsheet is
called "Angler of the year" and that will show the point value. The other
sheet is called "Clinton Lake" and this will show the total weight caught for
each member and the place they came in. So, When I enter 1st, 2nd, 3rd and so
on for each member then that will automatically enter the point value on the
"Angler of the year sheet". So, when I update one sheet then the other sheet
updates as well.

 

[T. Valko]

Here's a small sample file that demonstrates this.

Point distribution.xls 15kb

http://cjoint.com/?bctYR2dkOv

 

[ketchumbass]

Thanks. I will give this a shot and see what happens

 

[T. Valko]

You're welcome. Good luck!