VLOOKUP Round Down instead of Round Up?
Attached is a US Army 2 mile run standard scoring chart. The VLOOKUP function works well, except that it rounds up returned times and points, rather than rounding down.
For example. a time of 14:18 for a 42-26 year old male returns 98 points, which is correct.
A time of 14:12 for a 42-26 year old male returns 99 points, which is correct.
A time of 14:17, however, for a 42-46 year old male retuns 99 points, which is incorrect ... the runner should receive 98 points until achieving the 14:12 time threshold.
Hope I've explained this well enough. Thanks in advance.
VLOOKUP-BASIC-TIMES.xls
For example. a time of 14:18 for a 42-26 year old male returns 98 points, which is correct.
A time of 14:12 for a 42-26 year old male returns 99 points, which is correct.
A time of 14:17, however, for a 42-46 year old male retuns 99 points, which is incorrect ... the runner should receive 98 points until achieving the 14:12 time threshold.
Hope I've explained this well enough. Thanks in advance.
VLOOKUP-BASIC-TIMES.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! This solution works perfectly.
(Answer_Dude: The rounddown suggestion with the VLOOKUP / INDEX-MATCH function I needed to use didn't work.)
(Answer_Dude: The rounddown suggestion with the VLOOKUP / INDEX-MATCH function I needed to use didn't work.)
=ROUNDDOWN(vlookup(...),0)