+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

    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
    Attached Files Attached Files
    Last edited by aabbey; 11-12-2018 at 12:42 PM. Reason: Update Attachment

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

    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.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

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

    Sure. thanks for the response. I have filled the tables out how it ought to look once scoring has been applied.
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

    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.

  5. #5
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

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

    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
    Attached Files Attached Files
    Last edited by jeffreybrown; 11-14-2018 at 12:57 PM. Reason: Removed full quote!

  6. #6
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

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

    Is this something that is achievable within a fair time period to an unpaid helper, or would it take ages to programme this?

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

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

    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.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

    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?

  9. #9
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96
    Quote Originally Posted by Richard Buttrey View Post
    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?
    You are right, very well spotted.
    I'm not sure how much this matters but bear in mind the 13 doesn't represent seconds but relates to the VO2 metric. For example 13.4 would mean Level 13 lap 4.

    Regards
    Al

  10. #10
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

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

    Quote Originally Posted by Aardigspook View Post
    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.
    Thanks. Much Appreciation.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

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

    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: copy to clipboard
    Please Login or Register  to view this content.

    On the Body weight sheet the formula is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  12. #12
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

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

    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: copy to clipboard
    Please Login or Register  to view this content.

    In the Bodyweight table, the formula is this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    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!
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    10-17-2018
    Location
    Plymouth
    MS-Off Ver
    Excel 2021
    Posts
    96

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

    Thank you both, it is interesting to see the different ways of doing it. Thanks for the time clearly spent into looking into it and explaining.

    Regards

  14. #14
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

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

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

    Good luck at Lympstone.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Array of ranges, applying .clearcontents to each element
    By Sherpa21987 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2017, 06:47 PM
  2. How to create arts festival scoring sheet
    By stephensraj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2014, 04:12 PM
  3. [SOLVED] Trying to Create a Template for Scoring a Test
    By fasterpie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-02-2013, 01:20 PM
  4. Applying Named Ranges to Existing Formulae
    By Usman_W in forum Excel General
    Replies: 3
    Last Post: 06-26-2012, 01:59 PM
  5. Applying Formulae to Ranges
    By RexG in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-31-2011, 09:20 AM
  6. Replies: 2
    Last Post: 07-22-2010, 09:57 AM
  7. Applying named ranges to Pivot table
    By RBI in forum Excel General
    Replies: 5
    Last Post: 10-07-2008, 01:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1