Excel Help...

[wegoosewe]

I have a list of Par times that i am trying to create a templete.. i need to help writing the forumlas.. I think i am missing some thing so any help would be great. i will post a picture so you can see what i am doing and after that i will write the formulas.

[wegoosewe]

under the early Pace section i am trying to get excel to lookup the values for the 6 furlong call... so i have written """=if(a5=g8,vlookup(f5,e9:e50,1))....


so if a5 = g8 then lookup
f5 find in column F and return value
from column E

Anyhelp???

[Ray2000]

If you're trying to retrieve a value from column E using a value reference in column F

then in your case..

=IF(A5=G8,OFFSET(E8,MATCH(F5,$F$9:$F$50, 0),0,-1),"")

it's clunky and the match must be exact

hope this helps

[wegoosewe]

Ray2000---
what about if i have others to look up as well...like the 6.5,7,7.5 etc...

[Ray2000]

First, I need to correct my error in the formula, remove the---> ,-1 to now read

=IF(A5=G7,OFFSET(E8,MATCH(F5,$F$9:$F$50, 0),0),"")

Sorry, it was being ignored by excel anyway

Second, if you want to return the closest value in column "E" (no exact match found)then replace the first 0 with 1

=IF(A5=G7,OFFSET(E8,MATCH(F5,$F$9:$F$50, 1),0),"")

for this to work column F must be arranged smallest to largest it appears to be so.

Third,

Assuming your complete table is the range a8:m24 and there are or will be values under "6.5" cell "I8"
(and "7" and "7.5") and you still want the corresponding number in column "E" then the formula would be for "6.5"

=IF(A5=G8,OFFSET(E8,MATCH(F5,$I$9:$I$50, 0),0),"") ....same for "7" $K$9:$K%50 etc..

"Match" is returning the array index (how many rows down to find F5) then looks down that number of rows from reference point E8 OFFSET(E8

Hope this helps

[HUSKER55]

I am not a guru by any means and I am not familiar with OFFSET FUNCTION. If it works then don't change it.

I think it would be far easier down the road if you created a database workseet in your workbook and then refered each number to a specific column. The reason being it is easier to change a database than rewrite all the formulas and down the road you might need to change values in the database based on your results.

then all you would have to do is write a vlookup formula for each cell and the only thing that would change is the reference cell number.

just a thought.

husker55
:)

[wegoosewe]

RAY the new forumla looks like this..

=IF(A5=G8,OFFSET(E8,MATCH(F5,$F$9:$F$50,1),0),""),IF(A5=I8,OFFSET(E8,MATCH($H$9:$H$29,12),0)"")

problem now is that is says that it contains errors.. i dont understand i typed it the same way as the first one..

[Ray2000]

I agree with Husker55, there's probably better solutions

For now the problem is your nested if statement, should be...

=IF(A5=G8,OFFSET(E8,MATCH(F5,$F$9:$F$50,1),0), IF(A5=I8,OFFSET(E8,MATCH($H$9:$H$29,12),0) ,""))

consider the 2nd "IF" like an "else if" always checking another possible condition until finally at the end ,"")))))...)) nothing

[raybo]

I do something similar except I have a table that contains several tracks and several fractional par times for all the distances that are run at each track. I use the "lookup" function to find par times at particular race segments on particular tracks at particular race distances and then work with them from there.

Once you get times for each distance plugged in, try this formula to extract the par for each distance/half time: =if(A5=6,lookup(F5,F9:F24,E9:E24),if(A5=6.5,lookup (F5,H9:H24,E9:E24),if(A5=7,lookup(F5,J9:J24,E9:E24 ),if(A5=7.5,lookup(F5,L9:L24,E9:E24),0)

This will qualify the distance first, then lookup the actual running time from the correct (distance qualified) half times column, and return the par value for that (distance qualified) half time. The half times must be in ascending order for the lookup function to work correctly, and the actual time you're looking up must be contained in the half times column, otherwise Excel will lookup the largest time that is less than or equal to the actual time and return the par for that time.

If you want to add more distances than you show in your screenshot just add to the formula more nested "if" statements. If you run out of room in the formula cell then you'll have to continue the formula in another cell and in the first cell formula replace the final "0" with the continuing cell location.

I haven't tested this formula but it should work. If you have trouble with it just holler at me.