Forum Home
PC World Chat
Thread ID: 126090	2012-08-06 05:17:00	MS Excel formula for conditions?	caffy (2665)	PC World Chat

Post ID	Timestamp	Content	User
1293229	2012-08-06 05:17:00	Hi, Am trying to set up a MS Excel table for a points system, where for each person there's two columns, one for their placing (1st, 2nd, 3rd and so on), and the next column for their points . I have set up the table of placing and their allocated points on a column separate to the main table (but on the same sheet) . How do I set up the condition/IF rule, so if in the first column, I have put in say 1st, the adjacent text box will automatically show 20; if I type 2nd, it will show 16, etc . ? I am thinking that it will be something like =IF( . . . . . but unsure how to complete that formula .	caffy (2665)
1293230	2012-08-06 06:06:00	Is it a linear score system? i.e. 1st = 20, 2nd = 16, 3rd =12 etc? if so a simple formula would probably do it. you could use string commands to extract the number from the LHS of the entry and then calculate the value. I'm not at work to get the syntax of the string command but something like =20-((left(A1,1)-1)x4). So first would be 20-(1-1)x4)= 20, 2nd would be 20-(2-1)x4)=16, etc You could use a lookup table also, but I haven't used those much myself to know how.	dugimodo (138)
1293231	2012-08-06 06:57:00	Not quite linear, the system is something like 1st = 20, 2nd = 16, 3rd = 14, 4th = 12, 5th =10, 6th and so on 9,8,7. But I think I understand where you're going, I will give that a try. Oh, just had a thought - will the above command work if the numbers are not in the column already? And they won't be in order, for example first row the person may be 3rd, 2nd game they will be 5th, 3rd game 1st and so on. It is planned for this table to be updated every time the competition is held. So ideally the formula in the 2nd column will be set up already, and ready to function as soon as i enter something in whichever row.	caffy (2665)
1293232	2012-08-06 07:23:00	do a search for the vlookup function	wotz (335)
1293233	2012-08-07 11:51:00	I couldn't figure out how to get the vlookup to suit my needs as it looks up info in the column above; I need the information on the column to the right. I tried the =IF function and it looks like that's what I need: =IF(D6=1,"20") and I've just discovered how to add more variables to it: =IF(D6=1,"20",IF(D6=2,"16",IF(D6=3,"14", IF(D6=4,"10")))) quite complicated huh!	caffy (2665)
1293234	2012-08-08 06:04:00	do a search for the vlookup function +1	mikebartnz (21)
1