Multiple Formulas

L

LCpl Shaw

Im creating a database for my school house and before i convert over to
Access..Im trying to figure out some ways to calculate some numbers. For
example, my hardest thing yet, if someone was to run an 18min:00sec 3mile
run...he'll get a max score of 100. But if he ran it in 20min:34sec he'll get
a score of 84. Being the longest run of 28min:00sec (failing). How do i
create a formula to calculate the mins and sec to result in the point score.
 
N

Nick Hodge

Set up your timing ranges on another sheet and then use a VLOOKUP with the
last parameter set to TRUE. for example, if you table is a two column one
with times in one column (A) and the associated score in B then in your
current table (imagining the entered time is A) enter

=VLOOKUP(A1,SheetTable!$A$1:$B$100,2,TRUE)

this should return the nearest time and the corresponding score

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top