Using < or > and applying this to create scoring ranges.

Hello

I am trying to create a scoring system for a range of exercises.


On Sheet1 (RMFA scores) you will find the scoring system.

Sheet 2 (BFT VO2) contains 2 exercises set in data validation (BFT & VO2)

Sheet 3 (Bodyweight) contains 3 exercises which are also set using data validation (Press-Ups, Sit-Ups & Pull-Ups)


You will see on Sheets 2&3 columns named score which is where the appropriate score taken from the table on Sheet 1 RMFA Scores will go.


I have no idea where to start on this problem so any help is gratefully accepted.


Regards

You're going to have to talk us through this.

Manually add some example results and importantly tell us how you have calculated them explaining any rules.

Hi

Thanks for adding the scores. However you've not told us the most important bit. i.e. how have you calculated them. Please explain with reference to the original data and any rules you apply.

Remember you are completely au fait with your data and know exactly what it represent and how its used. We're coming to this with zero knowledge about your system and what it means so you'll have to hold our hands and talk it through.

Sure. Ill give some more information and hopefully that will help.

I and a few other lads are training to be Royal Marines and have a set of tests coming up. We have been encouraged to keep a journal to track our progress towards an acceptable target.

The test is called the PRMC and it consists of 6 tests, 5 of which can be trained for by reproducing the tests. The final one "assault course" cannot be accurately replicated and as such is omitted.


The 3 Sheets


Sheet 1 (RMFA Scores)
This sheet has the scoring.
On the far left of the sheet on the Y axis you have the different exercises .
On the Top of the sheet along the X axis you have the scoring and the scoring ranges.
There are no units of measurement put into this table, only figures however the units, or metric for each exercise are explained below.


Sheet 2 - VO2/BFT Sheet
This Sheet contains the journal of my practice runs where I will record my times, or level achieved in the tests that involve running.

Sheet 3 - Bodyweight
This Sheet contains the journal where I will record my scores of my practice runs of the tests that involve bodyweight exercises (pressusp,situps,pullups)

The Five Tests and how the they relate to the scoring are as follows.

BFT/VO2 - These two tests are the running tests

BFT - A mile and a half run - The scoring for this is based on the time achieved and is measured in mins/sec. For example
If I complete my run between the time frame of 10.30 - 9.46 I will score 2 points
If I am a bit quicker and come in at 9.30 then I will fit in the 9.45 - 9.00 and score three points,
If I have a bad day and come in at 11 mins I will score 1 point as I fit in the 10.31 - 11.15 bracket

The VO2 (Bleep Test) This test involves running between two points keeping in time with the bleep which gets progressively faster every ten lengths. Each length counts as 0.1. The scoring system isn't based on time, but levels achieved where 1.1 is the lowest and 22 is the highest. There is a scoring bracket though.

Anything below 10.5 is a fail and you might as well join the salvation army
Anything above 13 and you are superhuman
If I manage to get to level 11.3 I will score 2 points as I fit in the 10.9 - 11.5 range
If I get to level 12 .6 I will score 4 points as I fit in the 12.5 - 13.0 range

The Bodyweight Tests - Press-Ups, Sit-Ups and Pull-Ups

In each of these tests you are scored on the amount of repetitions you can perform of each exercise
for example
In the Press-Ups test if I manage to perform 45 Pressups I will score 4 points as I fall in the 40-49 range


If I score 55 Press-ups I will score the full five points because I fall in the 50-60 category



The same applies for the Situps and pull ups test however the scoring ranges vary as the expectation on what the human body can perform is different on the different muscle groups.


The scoring is how we will be assessed on the day, that is why there is a score column in sheets 2&3 as it better represents what we are trying to achieve.

I hope that helps clear up what I am trying to achieve.


I have updated the chart again so please fill latest attachment

Is that a typo in D14 of the BGT VO2 sheet?

Shouldn't a time of 13 seconds result in 4 points, i.e. I4:J4 on the RMFA table?

Your explanation above certainly makes sense to me - I can see what you want to achieve. It might need some re-arranging of how you arrange the data, but it's certainly possible. Personally I'm a bit busy with family issues right now, but I'll try to take a look at the weekend if no-one else gets to this before then.

This proposed solution makes a few changes to the table on the RMFA scores sheet as some lower limits were the same as the upper limits.
Also removed an extra space in cell N3 on the BFT VO2 sheet.
The array entered formulas* that display the scores 'By Formula' are in columns adjacent to the tables so as to show that they return the same values.
On the BFT VO2 sheet the formula is:

Formula:

=ROUNDUP(IF(B4="BFT",MATCH(C4,INDEX('RMFA Scores'!C$3:L$8,MATCH(B4,'RMFA Scores'!B$3:B$8,0),0),-1),MATCH(D4,INDEX('RMFA Scores'!C$3:L$8,MATCH(B4,'RMFA Scores'!B$3:B$8,0),0),1))/2,0)

On the Body weight sheet the formula is:

Formula:

=ROUNDUP(MATCH(D3,INDEX('RMFA Scores'!C$3:L$8,MATCH(C3,'RMFA Scores'!B$3:B$8,0),0),1)/2,0)

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. This needs to be done before copying the formula down the column.
Let us know if you have any questions.

Sorry for the delay - the weekend was a bit busier than planned, so I only got to this last night and then didn't have time to post this reply.
I've attached an amended version of your file with it all working - the explanation below is a summary of what's in there.

The Lookup function is good for comparing values with upper limits, so that's what I suggest using. The problem with it is that the lookup range must be in ascending order, which isn't the case for your BFT and Assault Course figures. So I broke the scoring table into two tables, one for 'Higher is Better' (VO2, Press-ups, Sit-ups and Pull-ups) and one for 'Lower is Better' (BFT and Assault course) which now goes from 5 to 1 instead of 1 to 5. I also moved the Upper and Lower limits on to different rows instead of adjacent columns.

The format of the Lookup function is: LOOKUP ( value being checked , range of values being checked against , equivalent results to be displayed ). So in this case, we want to do something like this: LOOKUP ( no of press-ups , range of press-up results , scores 1 to 5 ).

I decided to create a few Named Ranges for the scoring tables as well, to make it easier to see the ranges in each formula. For example, the Named Range 'R_Scores_LoGood' refers to 'RMFA Scores'!$D$14:$H$14. I've listed all the ranges next to the tables on the RMFA Scores sheet.

In the tables where you want the results, I decided to keep it simple by just using If statements to check the correct range - in other words, this sort of format: IF ( exercise is Press-ups , LOOKUP ( no of reps , in press-ups range , get score 1-5 ), ... etc with more If statements.
In your BFT/VO2 table, the formula used is therefore this:

Formula:

=IF([@[BFT/VO2]]=$I$3,LOOKUP([@Time],R_BFT_lower,R_Scores_LoGood),IF([@[BFT/VO2]]=$I$4,LOOKUP([@VO2],R_VO2_lower,R_Scores_HiGood)," "))

In the Bodyweight table, the formula is this:

Formula:

=IF([@Exercise]=$H$3,LOOKUP([@[Max Rep]],R_PressUps_lower,R_Scores_HiGood),IF([@Exercise]=$H$4,LOOKUP([@[Max Rep]],R_SitUps_lower,R_Scores_HiGood),IF([@Exercise]=$H$5,LOOKUP([@[Max Rep]],R_PullUps_lower,R_Scores_HiGood)," ")))

I have checked the results in each table against the figures in the scores tables and they're all correct using the figures there. I have not changed any of the values in your tables where the upper and lower limits are the same, as I'm not sure whether you want the upper or lower limit changed (for example, is 8.15 the upper limit of 5 or lower limit of 4 for BFT? I don't know and don't want you to get the wrong results.

I hope that does what you want. With JetMC's answer above, you now have a couple of options!

You're welcome, glad we could help and thanks for the rep.

Good luck at Lympstone.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.