Excel Formula - Score Based On Criteria

October 7, 2010 at 06:11:37
Specs: Windows XP
I am trying to return a score (0-100) based on four criteria (run time, waist measurement, push-ups, and sit-ups). Each criteria is assigned a value depending on run time, waist size, push-ups, and sit-ups. For instance, if i my run time was between 11:23 and 11:38 the component points would be 53.70, my waist size was 35.5" the component points would be 17.6, I did 48 push-ups for 9.2 point, and 51 sit-ups is 9.5 points. That would give me a total score of 90 out of 100 possible points. The Army uses a similar calculator in excel but the formulas are not visible. Any help would be appreciated.

See More: Excel Formula - Score Based On Criteria

Report •


#1
October 7, 2010 at 08:34:08
If you want the Army version, with the formulas viewable, go here:

http://www.apft.net/

On the right side panel, donwload APFT Calculator Excel

Once you get it downloaded and opened, just Unprotect the workbook.
No password is required, but the workbook must be unprotected to view the formulas.

It will initially display in a reduced form.

2000 & 2003
To Unprotect:
From the Tools menu,
Select Protection,
Unprotect Workbook.

2007
Select Review tab
Unprotect Workbook

MIKE

http://www.skeptic.com/


Report •

#2
October 7, 2010 at 08:43:18
It seems like this could be done with VLOOKUPs.

Since we don't know the "weighting" of each criteria we can't be exact, but you could build a table similar to the one below and SUM the values returned by VLOOKUP for each criteria.

If the criteria have a range of values that return a specific score, then you would use TRUE (1) as the range_lookup argument. See the Help files on VLOOKUP for a more detail explanation.

Using the values in the table below, this formula will return 90:

=VLOOKUP(C1, $A$2:$B$4, 2, 1) +
VLOOKUP(C6, $A$7:$B$9, 2, 1) +
VLOOKUP(C11, $A$12:$B$14, 2, 0) +
VLOOKUP(C16, $A$17:$B$19, 2, 0)

Obviously this is just to show you the concept. You will have to build your tables to match your value vs. score relationships.

	    A	      B	          C
1	      Run time		11:38
2	  11:00	     50	
3	  11:20	     53.7	
4	  11:40	     55	
5			
6	     Waist Size		 35.5
7	    34	     18	
8	    35	     17.6	
9	    36	     17	
10			
11	     Push-Ups		 48
12	   47	      9	
13	   48	      9.2	
14	   49	     10	
15			
16	     Sit-ups		 51
17	   50	     9	
18	   51	     9.5	
19	   52	    10	



Report •

#3
October 7, 2010 at 11:45:53
Thank you! I think I have the VLOOKUP thing down. But I do have one more value that I have to incorporate. Age groups, i.e. 17-30, 31-39, 40-49 etc. So depending on your age group, you can have the same run time, amt of push-ups etc. and have a different score. For instance, a 33 y/o who does 48 push-ups has a score of 9.2 and a 40 y/o has a score of 10 for the same amt. the push-up portion of my sheet looks like this:

Push Up Scoring

REPS AGE GROUP
17-30 31-39 40-49 50-59 60+
0 0 0 0 0 0
3 0 0 0 0 0
4 0 0 0 0 1
5 0 0 0 0 1.5
6 0 0 0 1 2
7 0 0 0 1.5 2.5
8 0 0 0 1.8 3
9 0 0 1 2 3.5
10 0 0 1.5 3 4
11 0 0 2 3.5 4.3
12 0 0 2.3 3.8 4.5
13 0 1 2.5 4 4.8
14 0 1.3 2.8 4.5 5
15 0 1.5 3 5 5.3
16 0 1.8 3.5 5.3 5.5
17 0 2 3.8 5.5 5.8
18 1 2.3 4 5.8 6
19 1.5 2.5 4.5 6 6.3
20 1.7 3 4.8 6.5 6.5
21 1.8 3.5 5 7 7
22 2 3.7 5.5 7.2 7.5
23 2.3 3.8 5.8 7.3 8


This is only a sample size. The sit-ups, run time and waist measurement will be similar. All help is greatly appreciated!


Report •

Related Solutions

#4
October 7, 2010 at 11:48:15
^ That looks nothing like what it did when I pasted it! The first column is # of reps. Age groups are at the top and the component scores fall under the age group columns respectively.

Report •

#5
October 7, 2010 at 12:11:01
To post with your columns lined up:

1 - Click on the pre symbol above the text entry box.
2 - Enter your data, in columns, between the pre tags
3 - Click "Preview Follow Up"
4 - Review how it looks and fix it in the text entry box below the Preview box.
5 - Click in the box next to "Check To Show Confirmation Page Again"
6 - Click either Confirm button to Preview the post again.
7 - Repeat step 4 - 6 as necessary until it looks the way you want.
8 - Click Confirm when you are satisfied.


Report •

#6
October 7, 2010 at 12:25:45
Try using the MATCH function to calculate which column the VLOOKUP should pull the data from:

I put the following ages in B2:F2:

17	31	40	50	60

I put the rest of the table in A3:F24

I entered an age in H2 (e.g. 59) and a number of Reps in I2 (e.g. 22)

This formula returned 7.2

=VLOOKUP(I2, $A$3:$F$24, MATCH(H2, $B$2:$F$2, 1)+1, 0)


Report •


Ask Question