'vlookup' and/or 'if' help!  
Author Message
bbranch





PostPosted: Tue Aug 16 16:39:26 CDT 2005 Top

worksheet functions >> 'vlookup' and/or 'if' help!

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

Excel49  
 
 
Ron





PostPosted: Tue Aug 16 16:39:26 CDT 2005 Top

worksheet functions >> 'vlookup' and/or 'if' help!

>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





PostPosted: Tue Aug 16 17:57:06 CDT 2005 Top

worksheet functions >> 'vlookup' and/or 'if' help!
In the attached spreadsheet, I used MATCH(). Hope it does what you
want.



lawdoggy Wrote:
> 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 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7094
View this thread: http://www.excelforum.com/showthread.php?threadid=396266

 
 
lawdoggy





PostPosted: Tue Aug 16 20:03:22 CDT 2005 Top

worksheet functions >> 'vlookup' and/or 'if' help! 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:


I really appreciate your time! thanks...mitch

 
 
lawdoggy





PostPosted: Tue Aug 16 20:23:36 CDT 2005 Top

worksheet functions >> 'vlookup' and/or 'if' help! 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





PostPosted: Wed Aug 17 06:52:37 CDT 2005 Top

worksheet functions >> 'vlookup' and/or 'if' help!

>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