If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If then formula
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 ** "photohoward1" wrote in message news 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
If then formula
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 "photohoward1" wrote in message news 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.518 / Virus Database: 316 - Release Date: 12-Sep-03 |
#3
|
|||
|
|||
If then formula
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 "photohoward1" wrote in message news 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
If then formula
how about =lookup(a1,{1,2,3},{400,375,325}) "photohoward1" wrote in message news 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|