'vlookup' and/or 'if' help!

[lawdoggy]

Hello...i'm in charge of conducting a physical training test and have a
table already created with the parameters of the test. Here is a
partial table:

% 1.5 mile 300m Bench sit-ups push vertical
99 9:52 42 1.72 62 72 30
95 10:34 48 1.40 50 60 25.5
90 11:20 50 1.30 47 51 24
85 11:55 52 1.18 45 50 22
80 12:30 53 1.11 43 44 21.5
75 12:56 54 1.04 42 41 21


what i would like to do is put in everyone's scores and have excel
check this table to tell me what percentage they received for that
score. i have been trying vlookup without success. i'm not sure how the
proper syntax would be to say:

Smith ran the 1.5 mile in 10 minutes 05 seconds. now this value in the
above table is between 99% and 95%. do i use vlook up for this? the
correct answer is 95% because the value falls to if the better one is
not reached.

any help would be greatly appeciated!

thanks...mitch

 

[Ron Rosenfeld]

Hello...i'm in charge of conducting a physical training test and have a
table already created with the parameters of the test. Here is a
partial table:

% 1.5 mile 300m Bench sit-ups push vertical
99 9:52 42 1.72 62 72 30
95 10:34 48 1.40 50 60 25.5
90 11:20 50 1.30 47 51 24
85 11:55 52 1.18 45 50 22
80 12:30 53 1.11 43 44 21.5
75 12:56 54 1.04 42 41 21


what i would like to do is put in everyone's scores and have excel
check this table to tell me what percentage they received for that
score. i have been trying vlookup without success. i'm not sure how the
proper syntax would be to say:

Smith ran the 1.5 mile in 10 minutes 05 seconds. now this value in the
above table is between 99% and 95%. do i use vlook up for this? the
correct answer is 95% because the value falls to if the better one is
not reached.

any help would be greatly appeciated!

thanks...mitch

There may be a better method to do this but:

The problem with VLOOKUP and MATCH is that they will match the lesser number,
so using one of those solutions will result in 99 for your example.

You can set the matching condition in an **array** formula, then use MATCH to
see when that condition becomes TRUE, and use that as an INDEX into the PerCent
array.

For example, for the distance events, you want to MATCH where the score is <=
the value in the column; for the others, you want to MATCH where the score is

= the value in the column.

Name your columns in the table to make the formulas easier to understand. I
used PerCent, Run and Swim for your %, 1.5 mile and 300m columns.

So for the 1.5mi and 300m, you could use this array formula:

=INDEX(PerCent,MATCH(TRUE,Score<=Run,0))
and
=INDEX(PerCent,MATCH(TRUE,Score<=Swim,0))

For the other events, you would use

=INDEX(PerCent,MATCH(TRUE,Score>=SitUps,0))

To enter an **array** formula, after typing it in, hold down <ctrl><shift>
while hitting <enter>. Excel will place braces {...} around the formula.


--ron

 

[Morrigan]

In the attached spreadsheet, I used MATCH(). Hope it does what you
want.

Hello...i'm in charge of conducting a physical training test and have a
table already created with the parameters of the test. Here is a
partial table:

% 1.5 mile 300m Bench sit-ups push vertical
99 9:52 42 1.72 62 72 30
95 10:34 48 1.40 50 60 25.5
90 11:20 50 1.30 47 51 24
85 11:55 52 1.18 45 50 22
80 12:30 53 1.11 43 44 21.5
75 12:56 54 1.04 42 41 21


what i would like to do is put in everyone's scores and have excel
check this table to tell me what percentage they received for that
score. i have been trying vlookup without success. i'm not sure how
the
proper syntax would be to say:

Smith ran the 1.5 mile in 10 minutes 05 seconds. now this value in
the
above table is between 99% and 95%. do i use vlook up for this? the
correct answer is 95% because the value falls to if the better one is
not reached.

any help would be greatly appeciated!

thanks...mitch

+-------------------------------------------------------------------+
|Filename: Physical.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3696 |
+-------------------------------------------------------------------+

 

[lawdoggy]

Hello Morrigan,

I clicked on the link you provided and it told it was an 'invalid
attachment specified'? could you please just email it to me:
(e-mail address removed)

I really appreciate your time! thanks...mitch

 

[lawdoggy]

Ron,

Thank you! it's working great. One more thing, how do I get it to not
put '#n/a' when there is no entry in that field. ie someone hasn't done
the 1.5 mile run yet?

mitch

 

[Ron Rosenfeld]

Ron,

Thank you! it's working great. One more thing, how do I get it to not
put '#n/a' when there is no entry in that field. ie someone hasn't done
the 1.5 mile run yet?

mitch

2 possible methods:

1. Use conditional formatting (Format/Conditional Formatting
Formula Is: =ISNA(A1)
Then format the font to be white, or the same color as whatever you are
using for a background color. The NA is still there; you just can't see it.

2. Wrap the original formula in an IF statement to check for a value in Score:

=IF(Score="","",=INDEX(PerCent,MATCH(TRUE,Score<=Run,0)))




--ron