Home/ Questions/Q 7165
Next
In Process
Computing Staff
  • 5
Asked: 2021-11-23T09:57:46+00:00 In:

Excel Formula – Score Based On Criteria

  • 5

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.

Share

    3 Answers

    1. 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

      • 0

    2. 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.

      • 0
    3. 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)

      • 0