If then formula

[photohoward1]

I am writing a spread sheet to keep track of a racing
club. I would like to be able to enter the place of finish in
one cell and have the points automatic in another.

1=400, 2=375, 3=325 and so forth

the place would be entered for example in cell A1
the points would automatically show up in cell B2

Help please

Howard

 

[Paul B]

Have a look at VLOOKUP in help and see if this will do what you want

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[TONY]

Name Rank Points This can be located anywhere
joe 2 =+VLOOKUP(B3,$E$4:$F$13,2,0) Rank Points
jim n =(+VLOOKUP(B4,$E$4:$F$13,2,0)) 1 400
john 1 =+VLOOKUP(B5,$E$4:$F$13,2,0) 2 375
james =+VLOOKUP(B6,$E$4:$F$13,2,0) 3 325
jerry 3 =+VLOOKUP(B7,$E$4:$F$13,2,0) 4 =+F6-50
johnny x =+VLOOKUP(B8,$E$4:$F$13,2,0) 5 =+F7-50
jonny 5 =+VLOOKUP(B9,$E$4:$F$13,2,0) 6 =+F8-50
7 =+F9-50
n 0
8 =+F10-50
9 =+F12-50




Name Rank Points This can be located anywhere
joe 2 375 Rank Points
jim n 0 1 400
john 1 400 2 375
james #N/A 3 325
jerry 3 325 4 275
johnny x #N/A 5 225
jonny 5 225 6 175
7 125
n 0
8 75
9 25


You can fill blanks with a letter such as n to mean 0 points or x or .
for "blank" so the Vlookup won't give #N/A

 

[Bill]

If you aren't planning on paying out too many places, you can use something
like this:

IF(A1=1,"400",IF(A1=2,"375",IF(A1=3,"325","300)))

basically this means "if A1 = 1, then put 400 in the cell (B2), otherwise if
it is = 2, put 375 in the cell, otherwise if it is = 3 put 325 in the cell.
If all of these are false put 300 in the cell.

Reason I say if you aren't planning on payout out a lot of places is becasue
Excel has a limitation of nested IFs. I think it is 7.

If you want to use all 7 of them, you continue the sequence with the last
being the final "False part". The IF function has three parts:

1 - Logical statement
2 - True part
3 - False part

If you had only two choices, you could have used:

IF(A1=1,"400","300"), which says use 400 if true or 300 if false. Since you
had more things to test for, a new IF statement becomes the "False" part.
Hopefully you get the idea. However, if you need several tests then the
VLOOKUP option mentioned earlier is a better choice.

Bill Foley
www.pttinc.com

 

[Don Guillett]

how about
=lookup(a1,{1,2,3},{400,375,325})

 

[Biff]

Hi Howard,

There are several ways to do this depending on how many
variables there are. Here's one way:

=CHOOSE(A1,400,375,325)

Whatever number you enter in A1 will return the
corresponding point value.

Biff

 

[CLR]

Check out the VLOOKUP function.......it's made to order for that sort of
thing....

Vaya con Dios,
Chuck, CABGx3

Hi Howard,

There are several ways to do this depending on how many
variables there are. Here's one way:

=CHOOSE(A1,400,375,325)

Whatever number you enter in A1 will return the
corresponding point value.

Biff

-----Original Message-----
I am writing a spread sheet to keep track of a racing
club. I would like to be able to enter the place of finish in
one cell and have the points automatic in another.

1=400, 2=375, 3=325 and so forth

the place would be entered for example in cell A1
the points would automatically show up in cell B2

Help please

Howard



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

 

[acw]

Howard

One way is to use the CHOOSE function.

=CHOOSE(A1,400,375,325)

Tony

 

[acw]

Howard

=CHOOSE(A1,400,375,325,...)


Tony

 

[Biff]

Hi Chuck,

Yes, Vlookup is another alternative. It looks like Howard
has plenty of choices!

The news server must have been down today. When I replied
there were no other replies. Coming back now, I see I was
one of the last to reply. One of the disadvantages of CDO!

Biff

-----Original Message-----
Check out the VLOOKUP function.......it's made to order for that sort of
thing....

Vaya con Dios,
Chuck, CABGx3

Hi Howard,

There are several ways to do this depending on how many
variables there are. Here's one way:

=CHOOSE(A1,400,375,325)

Whatever number you enter in A1 will return the
corresponding point value.

Biff

-----Original Message-----
I am writing a spread sheet to keep track of a racing
club. I would like to be able to enter the place of finish in
one cell and have the points automatic in another.

1=400, 2=375, 3=325 and so forth

the place would be entered for example in cell A1
the points would automatically show up in cell B2

Help please

Howard



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

.

 

[CLR]

Yeah Biff, sorry........guess I was kinda asleep at the switch a
little......I responded to what appeared to be an Original Post but was
apparently your response to a previous post, and I hadn't checked to see if
there were any previous posts......my bad, but I guess no harm done, as long
as the OP gets some answers to choose from, that's all that really matters,
even if some of them are the same.......as for posting times being delayed
for whatever reasons, I run into that too.....sometimes my list will go
several hours with no post updates, and then all of a sudden there is a
bunch coming in late..............some days, the Dragon wins.......

Vaya con Dios,
Chuck, CABGx3

Hi Chuck,

Yes, Vlookup is another alternative. It looks like Howard
has plenty of choices!
The news server must have been down today. When I replied
there were no other replies. Coming back now, I see I was
one of the last to reply. One of the disadvantages of CDO!

Biff

-----Original Message-----
Check out the VLOOKUP function.......it's made to order for that sort of
thing....

Vaya con Dios,
Chuck, CABGx3

Hi Howard,

There are several ways to do this depending on how many
variables there are. Here's one way:

=CHOOSE(A1,400,375,325)

Whatever number you enter in A1 will return the
corresponding point value.

Biff

-----Original Message-----
I am writing a spread sheet to keep track of a racing
club. I would like to be able to enter the place of
finish in
one cell and have the points automatic in another.

1=400, 2=375, 3=325 and so forth

the place would be entered for example in cell A1
the points would automatically show up in cell B2

Help please

Howard



------------------------------------------------
[/url]
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

.