Can Excel do this?

[TEX]

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

 

[Sandy Mann]

Construct a table somewhere on the sheet with two columns, first one times,
second one the words.

Start the times column from 0:00 and add the other times to give a table
with the time intervals that you want and the words that you want to
display. Then enter the formula:

=VLOOKUP(C1,D1:E7,2)

with the "overall time" in C1 and the table in D1:E7

the table can of course be anywhere on the sheet and can even be hidden by
hidine the olumns and it will still work.

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Conrad Carlberg]

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,"Experienced
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 here:

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