How can I quantify these fitness scores?

February 25, 2015 2:42 AM

Hey math/fitness nerds. I'm working on a pet project. I'm trying to quantify general fitness levels across a population based on a few different scores. I have the tests, but need help setting up the values of each score.

This question is not about the validity of these tests, it's about numbers. I'm open to discussion about the tests, but not here. (Please mail me if you have strong opinions about the tests.) This is strictly a math question.

I'm trying to give each score a value out of 100 so the scores can be displayed in a radar-style graph.

The tests are as follows:
Overhead squat mobility test. Score from 1-5 points. (5 = 100%)
1.5 mile run test time. 10 min 30s = 100% (630 seconds.)
500 meter ergometer row test time. 1 min 37 s = 100% (97 seconds)
1 Rep Max Deadlift. A weight of 1.75 bodyweight = 100%.

So here's an imaginary test subject:
Male
185 lbs
Squat evaluation = 4/5
1.5 mile run time = 12 minutes or 720 seconds.
500 meter row time = 2 min or 120 seconds.
1RM Deadlift = 225 lbs. At 185 lbs he'd need to pull 323.75 lbs.

So I'd like to know the best way to put each of the scores at a 100 point value. This is slightly tricky for me as obviously higher times would be worse than lower times.

Could someone help me with the formula in excel so I could just drop the scores into a spread sheet and get some answers? This is a basic math question not really an excel question. I know it's the sort of thing that's dead easy for some people but fairly mystifying for me.

To use the above examples:
Goal time for 1.5 miles is 10 minutes and 30 seconds. (630 seconds.) The subject ran 1.5 miles in 720 seconds. How can I set up the formula so that 720 seconds is scored out of 100 on a scale that uses 630 as a perfect score of 100 with anything larger being a worse score?

Thanks.

posted by Telf to Health & Fitness (5 answers total)

The problem with times is they have no upper bound. To set a score of 0-100 you would need to determine what 0 is and what 50 is.

Another option is to quantify speed which does have an upper bound. So 1.5 miles (7920 feet) in 630 seconds is an average speed of 12.57 feet per second.
If the subject ran it in 720 seconds then their speed was 11 feet per second.

So their score would be (11/12.57)*100=87.5
The general formula if they ran 1.5 miles in X seconds is: ((7920/X)/12.57)*100 = 792000/(X*12.57)
posted by vacapinta at 3:39 AM on February 25, 2015 [3 favorites]

Response by poster: Hmm, interesting point. Thanks. I see what you're saying. The score is really more of a threshold, so anything below that time would be considered passing or good enough.

For more context, the 1.5 mile test is a standardized test used by organizations like the ACSM. There are actually tables available.

Here's a link for one table in pdf form. It's not the original table I used so the numbers look slightly different. Essentially I wanted a passing score to be in the top 75%. Instead of including the whole table, I told people to shoot for a time below 10:30 or keep on training til the hit that time.

In case you were wondering the top 99% time was 8:22 and the bottom 1% was 20:55 among males age 20-29. N= 2,606.

That being said, I think your solution would work well for my purposes.
posted by Telf at 4:23 AM on February 25, 2015

Best answer: There isn't one way to do this - you have to choose a function that converts each test result (a domain) into the desired "normalized" score range from 0-100. There are many functions you could choose, and each will make your data look a little different. Below I've written some simple, linear functions with cutoffs that do what you are asking for.

Let's set up your spreadsheet so each row is a test subject, and each column is a different test result:

Column A: Overhead squat mobility test (as a score from 1-5)
Column B: 1.5 mile run test time (in seconds)
Column C: 500 meter ergometer row test time (in seconds)
Column D: 1 Rep Max Deadlift (as a fraction of bodyweight)

Then we will create formulas in columns E-H corresponding respectively to columns A-D which will give us our normalized score.

Cell E1: =A1*20

Cell F1: =IF(B1 < 630, 100, IF(B1 > 2000, 0, 100 - (B1-630)*100/(2000-630)))

Note that I had to choose a value that would correspond to a score of 0 - I picked 2000 seconds, but you can change that. All the IF statements are designed to score any values under 630 as 100, and any values over 2000 as 0.

Cell G1: =IF(C1 < 97, 100, IF(C1 > 300, 0, 100 - (C1-97)*100/(300-97)))

Note that I had to choose a value that would correspond to a score of 0 - I picked 300 seconds, but you can change that. All the IF statements are designed to score any values under 97 as 100, and any values over 300 as 0.

Cell H1: =IF(D1 > 1.75, 100, D1*100/1.75)

The IF statement here maps any weight over 1.75 to a score of 100.

Once you have those cells set up, you can simply click and drag the formula in the first row to fill up many rows in columns E-H, or alternatively you can look up how to create an array formula - either one will work fine.
posted by Salvor Hardin at 7:19 AM on February 25, 2015 [1 favorite]

How much data do you have?

What are you trying to characterize relative to? Are you trying to characterize individuals vs the whole population? Groups of people with specific characteristics vs the whole population?

And, do you need the results to even try to mean anything at all?

That question is not intended to be snarky or mean, but statistics is tricky. And doing distributions right may well involve calculus or near that level of math skill.

I'm not a statistician, but if I were trying to score an individual based on a population, I'd figure out some sort of distribution of scores that I wanted, and then make a function that map the test results to each score such that the number of people who do worse than a given time/weight on the test is equal to the number of people who I want score less than that number on the test.

For example, if I wanted the average test score to be 50, and half the people got worse than 11 minutes on the running, the function would convert 11 minutes to a score of 50.

The details of how to do that could get complicated.

Though if your answer to the third question is "not enough to do messy math" then Salvor Hardin's answer looks pretty good and simple.
posted by Zalzidrax at 8:17 AM on February 25, 2015

It kinda sounds like you want to calculate the percentile. Excel has a built-in function for this, tho I'd have too look up how to do it. It's probably easiest if you're willing to rank the best score as 100%, rather than using a high score that nobody actually achieved...
posted by grateful at 6:44 PM on February 25, 2015