Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can Excel do this?
Greetings, I am new to this forum and have a question about what Excel is capable of. I asked around with friends and co-worker only to discover that I know more about Excel than they do. I created a sport specific score sheet (scores based on time) in which stage times, time penalties, etc. are summed and placed in an “overall time” cell. So far so good (everything works as planned). Now what I wish to do is have, not a number, but a word such as “novice” show up in a “rating” cell if the value in the “overall time” cell falls between two time limits and a different word such as “expert” show up in the “rating” cell if the value in the “overall time” cell falls between another range of times. There will be 6 ratings all together. I played with bracket, less than, greater than, or?, etc, but nothing seems to work and I find nothing on it in the help section that comes with the program. I don’t know if Excel is even capable of doing this, although I suspect that it is. I could use some help. I don’t expect a detailed explanation, although that would be nice, but I am hoping for some guidance as to exactly where I might find instructions aimed at a seasoned novice. Thanks - TEX -- TEX ------------------------------------------------------------------------ TEX's Profile: http://www.excelforum.com/member.php...o&userid=26720 View this thread: http://www.excelforum.com/showthread...hreadid=399752 |
#3
|
|||
|
|||
Hi Tex,
Yes, Excel can do what you describe. But I'm pretty sure I wouldn't use VLOOKUP. It appears more efficient than what I'm about to suggest, but you need to make certain that your overall time values are in ascending order. That's an easy condition to miss. However, if you're willing to make certain of ascending order, then VLOOKUP is surely easier. Also, be sure to use =VLOOKUP(C1,$D$1:$E$7,2) instead of =VLOOKUP(C1,D1:E7,2), or similar, so that you can copy-and-paste the formula without inadvertently changing the lookup table's address. As an alternative, suppose that one of your overall-time values is in A4, another in A10, another in A18, and so on. (The actual cell addresses are irrelevant; I specify them only to make the explanation more straightforward.) In, say, cell B4, you could enter this formula: =IF(A4<0.2,"Novice",IF(A4<0.4,"Tyro",IF(A4<0.6,"Ex perienced Player",IF(A4<0.7,"Highly Skilled",IF(A4<0.8,"LaCrosse Junkie","MVP"))))) You can now copy-and-paste that formula into B10, B18, and so one -- that is, cells immediately adjacent to the cell in column A where you have the overall-time values. The immediate adjacency is not required, just convenient. A few things to notice he 1. Excel's IF function uses this syntax: =IF(condition, value if condition is TRUE, value if condition is FALSE). Suppose that A4 contains the value 0.15. When you point the above formula at A4, the first IF's condition (that is, A4<.2) returns the value TRUE, so the function returns the value "Novice". 2. Suppose that A4 contains the value .25. Now, the first IF's condition returns the value FALSE, because A4's value of .25 is greater than 0.2. Therefore, Excel returns the first IF function's third argument -- that is, "value if condition is FALSE"). 3. That third argument is yet another IF function, which Excel proceeds to evaluate, and returns the second IF's second argument, "Tyro" when A4's value is, say. .3, or the second IF's third argument, yet another IF, when A4's value is, say, .42. 4. "MVP" is returned if none of the preceding conditions is satisfied. These are termed "nested functions," or, specific to the problem you pose, "nested IFs." C^2 Conrad Carlberg -- Excel Sales Forecasting for Dummies, Wiley, 2005 "TEX" wrote in message ... Greetings, I am new to this forum and have a question about what Excel is capable of. I asked around with friends and co-worker only to discover that I know more about Excel than they do. I created a sport specific score sheet (scores based on time) in which stage times, time penalties, etc. are summed and placed in an "overall time" cell. So far so good (everything works as planned). Now what I wish to do is have, not a number, but a word such as "novice" show up in a "rating" cell if the value in the "overall time" cell falls between two time limits and a different word such as "expert" show up in the "rating" cell if the value in the "overall time" cell falls between another range of times. There will be 6 ratings all together. I played with bracket, less than, greater than, or?, etc, but nothing seems to work and I find nothing on it in the help section that comes with the program. I don't know if Excel is even capable of doing this, although I suspect that it is. I could use some help. I don't expect a detailed explanation, although that would be nice, but I am hoping for some guidance as to exactly where I might find instructions aimed at a seasoned novice. Thanks - TEX -- TEX ------------------------------------------------------------------------ TEX's Profile: http://www.excelforum.com/member.php...o&userid=26720 View this thread: http://www.excelforum.com/showthread...hreadid=399752 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |