Formula to get speed rating

[gfnut]

Hi I am new to excel and would to find a formula or function to find a speed rating. I would like to input distance + time and beaten lengths to get rating.

SpeedRatingGrid.xlsx
	A	B	C	D	E	F	G	H	I		K	L	M	N	O	P	Q	R	S	T
1	Rating	3F	4F	4.5F	5F	5.5F	6F	6.5F	7F		8F	8.3F	8.5F	9F	10F	12F		Lengths into Seconds Table
2	100	31.1	43.3	49.2	55.2	61.2	67.4	113.5	119.3		133.2	137.2	139.4	145.7	158.3	223.9		Nose	0
3	99.5					61.3		113.6			133.3	137.3	139.5	145.8	158.4	224		Head	0
4	99	31.2	43.4	49.3	55.3	61.4	67.5	113.7	119.4		133.4	137.4	139.6	145.9	158.5	224.2		Neck	0
5	98.5						67.6		119.5		133.5	137.5	139.7	146	158.6	224.3		½	0.1
6	98	31.3	43.5	49.4	55.4	61.5	67.7	113.8	119.6		133.6	137.6	139.8	146.1	158.7	224.4		¾	0.1
7	97.5				55.5	61.6		113.9	119.7		133.7	137.7	139.9	146.2	158.8	224.5		1	0.2
8	97	31.4	43.6	49.5	55.6	61.7	67.8	114	119.8		133.8	137.8	140	146.3	158.9	224.6		1 ¼	0.2
9	96.5						67.9	114.1	119.9		133.9	137.9	140.1	146.4	159	224.7		1 ½	0.3
10	96		43.7	49.6	55.7	61.8	68	114.2	120		134	138	140.2	146.5	159.1	224.8		1 ¾	0.3
11	95.5										134.1	138.1	140.3	146.6	159.2	224.9		2	0.4
12	95	31.5	43.8	49.7	55.8	61.9	68.1	114.3	120.1		134.2	138.2	140.4	146.7	159.3	225		2 ¼	0.4
13	94.5					62	68.2	114.4	120.2		134.3	138.3	140.5	146.8	159.4	225.1		2 ½	0.5
14	94	31.6	43.9	49.8	55.9	62.1	68.3	114.5	120.3		134.4	138.4	140.6	146.9	159.5	225.2		2 ¾	0.5
15	93.5				56			114.6	120.4		134.5	138.5	140.7	147	159.6	225.3		3	0.6
16	93	31.7	44	49.9	56.1	62.2	68.4	114.7	120.5		134.6	138.6	140.8	147.1	159.7	225.4		3 ¼	0.6
17	92.5						68.5		120.6		134.7	138.7	140.9	147.2	159.8	225.5		3 ½	0.7
18	92		44.1	50	56.2	62.3	68.6	114.8	120.7		134.8	138.8	141	147.3	159.9	225.6		3 ¾	0.7
19	91.5					62.4		114.9			134.9	138.9	141.1	147.4	200	225.7		4	0.8
20	91	31.8	44.2	50.1	56.3	62.5	68.7	115	120.8		135	139	141.2	147.5	200.1	225.8		4 ¼	0.8
21	90.5						68.8		120.9		135.1	139.1	141.3	147.6	200.2	225.9		4 ½	0.9
22	90	31.9	44.3	50.2	56.4	62.6	68.9	115.1	121		135.2	139.2	141.4	147.7	200.3	226		4 ¾	0.9
23	89.5				56.5	62.7		115.2	121.1		135.3	139.3	141.5	147.8	200.4	226.1		5	1
24	89	32	44.4	50.3	56.6	62.8	69	115.3	121.2		135.4	139.4	141.6	147.9	200.5	226.2		5 ½	1.1
25	88.5						69.1	115.4	121.3		135.5	139.5	141.7	148	200.6	226.3		6	1.2
26	88		44,5	50.4	56.7	62.9	69.2	115.5	121.4		135.6	139.6	141.8	148.1	200.7	226.4		6 ½	1.3
27	87.5										135.7	139.7	141.9	148.2	200.8	226.5		7	1.4
28	87	32.1	44.6	50.5	56.8	63	69.3	115.6	121.5		135.8	139.8	142	148.3	200.9	226.6		7 ½	1.5
29	86.5					63.1	69.4	115.7	121.6		135.9	140	142.1	148.4	201	226.7		8	1.6
30	86	32.2	44.7	50.6	56.9	63.2	69.5	115.8	121.7		136	140.1	142.2	148.5	201.1	226.8		8 ½	1.7
31	85.5				57			115.9	121.8		136.1	140.2	142.3	148.6	201.2	226.9		9	1.8
32	85	32.3	44.8	50.7	57.1	63.3	69.6	116	121.9		136.2	140.3	142.4	148.7	201.3	227		9 ½	1.9
33	84.5						69.7		122		136.3	140.4	142.5	148.8	201.4	227.1		10	2
34	84		44.9	50.8	57.2	63.4	69.8	116.1	122.1		136.4	140.5	142.6	148.9	201.5	227.2		11	2.2
35	83.5					63.5		116.2			136.5	140.6	142.7	149	201.6	227.3		12	2.4
36	83	32.4	45	50.9	57.3	63.6	69.9	116.3	122.2		136.6	140.7	142.8	149.1	201.7	227.4		13	2.6
37	82.5						70		122.3		136.7	140.8	142.9	149.2	201.8	227.5		14	2.8
38	82	32.5	45.1	51	57.4	63.7	70.1	116.4	122.4		136.8	140.9	143	149.3	201.9	227.6		15	3
39	81.5				57.5	63.8		116.5	122.5		136.9	141	143.1	149.4	202	227.7		16	3.2
40	81	32.6	45.2	51.1	57.6	63.9	70.2	116.6	122.6		137	141.1	143.2	149.5	202.1	227.8		17	3.4
41	80.5						70.3	116.7	122.7		137.1	141.2	143.3	149.6	202.2	227.9		18	3.6
42	80		45.3	51.2	57.7	64	70.4	116.8	122.8		137.2	141.3	143.4	149.7	202.3	228		19	3.8
43	79.5										137.3	141.4	143.5	149.8	202.4	228.1		20	4
44	79	32.7	45.4	51.3	57.8	64.1	70.5	116.9	122.9		137.4	141.5	143.6	149.9	202.5	228.2		25	5
45	78.5					64.2	70.6	117	123		137.5	141.6	143.7	150	202.6	228.3		30	6
Sheet1

 

[awoohaw]

As far as the "Beaten Lengths" how do you want that incorporated into the function? Is the lookup for that added or subtracted to the rating? Give me the math and I'll see what I can do.

 

[gfnut]

Thank you

 

[awoohaw]

Thank you

You are welcome! But, are you going to give me the math for the beaten by? Does that calculation (the second xl2bb snippet) work?

 

[gfnut]

Yes. The chart in the xl2bb would be the length they were beaten = the tenths of seconds added to the time.

examble
1 length =.2 added to time and then match rating.

 

[awoohaw]

Okay, I think this will do the trick:

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Rating	3F	4F	4.5F	5F	5.5F	6F	6.5F	7F	Unknown	8F	8.3F	8.5F	9F	10F	12F		Lengths into Seconds Table		1
2	100	31.1	43.3	49.2	55.2	61.2	67.4	113.5	119.3		133.2	137.2	139.4	145.7	158.3	223.9		Nose	0		Distance	3F
3	99.5					61.3		113.6			133.3	137.3	139.5	145.8	158.4	224		Head	0		Time	31.49
4	99	31.2	43.4	49.3	55.3	61.4	67.5	113.7	119.4		133.4	137.4	139.6	145.9	158.5	224.2		Neck	0		Beaten Lengths	9 ½
5	98.5						67.6		119.5		133.5	137.5	139.7	146	158.6	224.3		½	0.1			31
6	98	31.3	43.5	49.4	55.4	61.5	67.7	113.8	119.6		133.6	137.6	139.8	146.1	158.7	224.4		¾	0.1
7	97.5				55.5	61.6		113.9	119.7		133.7	137.7	139.9	146.2	158.8	224.5		1	0.2		Rating:	79
8	97	31.4	43.6	49.5	55.6	61.7	67.8	114	119.8		133.8	137.8	140	146.3	158.9	224.6		1 ¼	0.2
9	96.5						67.9	114.1	119.9		133.9	137.9	140.1	146.4	159	224.7		1 ½	0.3
10	96		43.7	49.6	55.7	61.8	68	114.2	120		134	138	140.2	146.5	159.1	224.8		1 ¾	0.3
11	95.5										134.1	138.1	140.3	146.6	159.2	224.9		2	0.4
12	95	31.5	43.8	49.7	55.8	61.9	68.1	114.3	120.1		134.2	138.2	140.4	146.7	159.3	225		2 ¼	0.4
13	94.5					62	68.2	114.4	120.2		134.3	138.3	140.5	146.8	159.4	225.1		2 ½	0.5
14	94	31.6	43.9	49.8	55.9	62.1	68.3	114.5	120.3		134.4	138.4	140.6	146.9	159.5	225.2		2 ¾	0.5
15	93.5				56			114.6	120.4		134.5	138.5	140.7	147	159.6	225.3		3	0.6
16	93	31.7	44	49.9	56.1	62.2	68.4	114.7	120.5		134.6	138.6	140.8	147.1	159.7	225.4		3 ¼	0.6
17	92.5						68.5		120.6		134.7	138.7	140.9	147.2	159.8	225.5		3 ½	0.7
18	92		44.1	50	56.2	62.3	68.6	114.8	120.7		134.8	138.8	141	147.3	159.9	225.6		3 ¾	0.7
19	91.5					62.4		114.9			134.9	138.9	141.1	147.4	200	225.7		4	0.8
20	91	31.8	44.2	50.1	56.3	62.5	68.7	115	120.8		135	139	141.2	147.5	200.1	225.8		4 ¼	0.8
21	90.5						68.8		120.9		135.1	139.1	141.3	147.6	200.2	225.9		4 ½	0.9
22	90	31.9	44.3	50.2	56.4	62.6	68.9	115.1	121		135.2	139.2	141.4	147.7	200.3	226		4 ¾	0.9
23	89.5				56.5	62.7		115.2	121.1		135.3	139.3	141.5	147.8	200.4	226.1		5	1
24	89	32	44.4	50.3	56.6	62.8	69	115.3	121.2		135.4	139.4	141.6	147.9	200.5	226.2		5 ½	1.1
25	88.5						69.1	115.4	121.3		135.5	139.5	141.7	148	200.6	226.3		6	1.2
26	88		44.5	50.4	56.7	62.9	69.2	115.5	121.4		135.6	139.6	141.8	148.1	200.7	226.4		6 ½	1.3
27	87.5										135.7	139.7	141.9	148.2	200.8	226.5		7	1.4
28	87	32.1	44.6	50.5	56.8	63	69.3	115.6	121.5		135.8	139.8	142	148.3	200.9	226.6		7 ½	1.5
29	86.5					63.1	69.4	115.7	121.6		135.9	140	142.1	148.4	201	226.7		8	1.6
30	86	32.2	44.7	50.6	56.9	63.2	69.5	115.8	121.7		136	140.1	142.2	148.5	201.1	226.8		8 ½	1.7
31	85.5				57			115.9	121.8		136.1	140.2	142.3	148.6	201.2	226.9		9	1.8
32	85	32.3	44.8	50.7	57.1	63.3	69.6	116	121.9		136.2	140.3	142.4	148.7	201.3	227		9 ½	1.9
33	84.5						69.7		122		136.3	140.4	142.5	148.8	201.4	227.1		10	2
34	84		44.9	50.8	57.2	63.4	69.8	116.1	122.1		136.4	140.5	142.6	148.9	201.5	227.2		11	2.2
35	83.5					63.5		116.2			136.5	140.6	142.7	149	201.6	227.3		12	2.4
36	83	32.4	45	50.9	57.3	63.6	69.9	116.3	122.2		136.6	140.7	142.8	149.1	201.7	227.4		13	2.6
37	82.5						70		122.3		136.7	140.8	142.9	149.2	201.8	227.5		14	2.8
38	82	32.5	45.1	51	57.4	63.7	70.1	116.4	122.4		136.8	140.9	143	149.3	201.9	227.6		15	3
39	81.5				57.5	63.8		116.5	122.5		136.9	141	143.1	149.4	202	227.7		16	3.2
40	81	32.6	45.2	51.1	57.6	63.9	70.2	116.6	122.6		137	141.1	143.2	149.5	202.1	227.8		17	3.4
41	80.5						70.3	116.7	122.7		137.1	141.2	143.3	149.6	202.2	227.9		18	3.6
42	80		45.3	51.2	57.7	64	70.4	116.8	122.8		137.2	141.3	143.4	149.7	202.3	228		19	3.8
43	79.5										137.3	141.4	143.5	149.8	202.4	228.1		20	4
44	79	32.7	45.4	51.3	57.8	64.1	70.5	116.9	122.9		137.4	141.5	143.6	149.9	202.5	228.2		25	5
45	78.5					64.2	70.6	117	123		137.5	141.6	143.7	150	202.6	228.3		30	6
Rev Lookup Get Row Header

Cell Formulas
Range	Formula
V5	V5	=MATCH(V4,BeatenLengths,0)
V7	V7	=INDEX($A$1:$A45,MATCH( V3 + INDEX(S$2:$S$45,MATCH(V4,$R$2:$R$45,0)),INDEX($A$1:$P$45,1,MATCH(V2,$A$1:$P$1,0)): INDEX($A$1:$P45,ROWS($A$1:$A$45),MATCH(V2,$A$1:$P$1,0)),1))

Named Ranges
Name	Refers To	Cells
BeatenLengths	='Rev Lookup Get Row Header'!$R$2:$R$45	V7, V5
Distance	='Rev Lookup Get Row Header'!$B$1:$P$1	V7
Rating	='Rev Lookup Get Row Header'!$A$1:$A$45	V7

Cells with Data Validation
Cell	Allow	Criteria
V2:W2	List	=Distance
V4	List	=BeatenLengths

A couple comments: In your "field data" you need to make sure that you:
1. only have empty cells or numbers. There were one or two text values.
2. The matching functions are set to find the exact or next lowest number, so each column must be in ascending order, which for your purposes makes sense anyway.
3. The Input boxes should not be copied and pasted somewhere else. If you want to place them somewhere elese in the the workbook you should CUT then paste. If you copy the cell references will get messed up.

 

[awoohaw]

Oh I left a named range references in.

These are for
1. The drop down list (Data Validation) which you can change to the cell reference

$B$1:$P$1 for distance​

$R$2:$R$45 for beaten length​

There is a named range for rating, i'm not sure if used it in any calculations but here is the reference:
A$1:$A$45 for rating.

2. Beaten Length is used in a test cell (V5) you can delete that cell as it is not used in any calcuation.

 

[gfnut]

It looks like everything is working. In cell V5 I get #NAME?. Can I get rid of that some how?

 

[awoohaw]

It looks like everything is working. In cell V5 I get #NAME?. Can I get rid of that some how?

Yes, You should be able to delete V5 and not have any issues. You can also go into "Name Manager" (From Menu >>Formulas (Defined Names Section)>>Name Manager>> And delete any names that I may have had in the worksheet. It should change the name references in cells to their cell/range address references. If not undo!

If you find that there is a solution in the thread please mark the message number that has been the best solution you.

 

[awoohaw]

Yes, You should be able to delete V5 and not have any issues. You can also go into "Name Manager" (From Menu >>Formulas (Defined Names Section)>>Name Manager>> And delete any names that I may have had in the worksheet. It should change the name references in cells to their cell/range address references. If not undo!

If you find that there is a solution in the thread please mark the message number that has been the best solution you.

I just double checked... If you delete the named ranges "Distance" and "Beaten Lengths"you will mess up your data validation. You do not need data validation, but it helps.
To add data validation for distance without a named range.. select the cell that the distance is entered into. From Menu click Data, in the Data Tools sections click Data Validation. A GUI will pop up. In the Allow Box select "LIST", then in the Source Box type the cell range listed above or use the mouse to select the Column Headers that have the distances (the top row of the grid... only distances not the top corner cell) This should be the same range of cells that are seen at the bottom of the worksheet snippet.
Do the same thing for Beaten Lengths except select the column of cells that contain the beaten lengths.

 

[gfnut]

Thank You so much for your help.