Index formula

M

mattbird

Active Member
Joined
Oct 15, 2013
Messages
305
Office Version
  1. 2016
Hi,

I am have a table (Excel 2003) that I would like to return an answer from another table, but it needs to take into consideration gender and age. I have written a Index formula that works for age 0-29 male & Female, but am now stuck as I can't work out how to make it return an answer is the person is ie 30-34, 35-39, 40-44, 45-49, 50-54, 55-59, 60-64 as well as taking their gender into consideration.

The formula I am working with is:
Code: 
=IF($F312="M",(IF(P312=0,0,IF(E312<=30,IF(P312>=8.25,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$B5:$B55))),100)))),(IF(P312=0,0,(IF(P312>=10,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$F5:$F55))),100)))))

It is frustrating as everything I have tried doesn't work.

Any help would be greatly appreciated.

Matt
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi,

I have also got stuck at the same point trying with a vlookup, but I read that the index formula is better that a vlookup as it is quicker.

regards
 
Upvote 0
mattbird said:
Hi,

I am have a table (Excel 2003) that I would like to return an answer from another table, but it needs to take into consideration gender and age. I have written a Index formula that works for age 0-29 male & Female, but am now stuck as I can't work out how to make it return an answer is the person is ie 30-34, 35-39, 40-44, 45-49, 50-54, 55-59, 60-64 as well as taking their gender into consideration.

The formula I am working with is:
Code: 
=IF($F312="M",(IF(P312=0,0,IF(E312<=30,IF(P312>=8.25,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$B5:$B55))),100)))),(IF(P312=0,0,(IF(P312>=10,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A5:$A55,MATCH(P312,'PFA Conv Table - 1.5 Mile Run'!$F5:$F55))),100)))))

It is frustrating as everything I have tried doesn't work.

Any help would be greatly appreciated.

Matt
Click to expand...

Please provide an example of your table(s) as well as detailing the result you would like to see.

Matty
 
Upvote 0
Thanks for your help.

I am not sure how insert a copy of my table on here?

Matt
 
Upvote 0
Matty,

Table 1 is the input table and Table 2 (next post) is where it looks for the result.

Table 1

Excel 2007
DEFGHIJKLMNOPQRST

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: #C0C0C0, align: center"]DOB[/TD]
[TD="bgcolor: #C0C0C0, align: center"]AGE[/TD]
[TD="bgcolor: #C0C0C0, align: center"]M/F[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SQN[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Date of[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Date of Next[/TD]
[TD="bgcolor: #C0C0C0, align: center"]PRESS - UPS[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]SIT - UPS[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]2.4km TIMED RUN[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]COY[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Assessment [/TD]
[TD="bgcolor: #C0C0C0, align: center"]Assessment[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SCORE[/TD]
[TD="bgcolor: #C0C0C0, align: center"]POINTS[/TD]
[TD="bgcolor: #C0C0C0, align: center"]RESULT[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SCORE[/TD]
[TD="bgcolor: #C0C0C0, align: center"]POINTS[/TD]
[TD="bgcolor: #C0C0C0, align: center"]RESULT[/TD]
[TD="bgcolor: #C0C0C0, align: center"]D.MIN[/TD]
[TD="bgcolor: #C0C0C0, align: center"]MIN[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SEC[/TD]
[TD="bgcolor: #C0C0C0, align: center"]POINTS[/TD]
[TD="bgcolor: #C0C0C0, align: center"]RESULT[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFF99, align: center"]11/04/90[/TD]
[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFF99, align: center"]M[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]10.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFF99, align: center"]70[/TD]
[TD="bgcolor: #FFFF99, align: center"]P[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

</tbody>
PFA​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]S14[/TH]
[TD="align: left"]=IF($F14="M",(IF(P14=0,0,IF(E14<=30,IF(P14>=8.25,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A3:$A53,MATCH(P14,'PFA Conv Table - 1.5 Mile Run'!$B3:$B53))),100)))),(IF(P14=0,0,(IF(P14>=10,(INDEX('PFA Conv Table - 1.5 Mile Run'!$A3:$A53,MATCH(P14,'PFA Conv Table - 1.5 Mile Run'!$F3:$F53))),100)))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]T14[/TH]
[TD="align: left"]=IF($P14=0,"",(IF($F14="M",(IF(P14<=10.5,"P","F")),IF(P14<=13,"P","F"))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E14[/TH]
[TD="align: left"]=IF(D14="","",DATEDIF(D14,NOW(),"Y"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E15[/TH]
[TD="align: left"]=IF(D15="","",DATEDIF(D15,NOW(),"Y"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E16[/TH]
[TD="align: left"]=IF(D16="","",DATEDIF(D16,NOW(),"Y"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E17[/TH]
[TD="align: left"]=IF(D17="","",DATEDIF(D17,NOW(),"Y"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I14[/TH]
[TD="align: left"]=IF(H14="","",DATE(YEAR(H14),MONTH(H14)+IF(Y14="Fail",0,6),DAY(H14)+IF(Y14="Fail",7,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I15[/TH]
[TD="align: left"]=IF(H15="","",DATE(YEAR(H15),MONTH(H15)+IF(Y15="Fail",0,6),DAY(H15)+IF(Y15="Fail",7,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I16[/TH]
[TD="align: left"]=IF(H16="","",DATE(YEAR(H16),MONTH(H16)+IF(Y16="Fail",0,6),DAY(H16)+IF(Y16="Fail",7,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I17[/TH]
[TD="align: left"]=IF(H17="","",DATE(YEAR(H17),MONTH(H17)+IF(Y17="Fail",0,6),DAY(H17)+IF(Y17="Fail",7,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]P14[/TH]
[TD="align: left"]=$Q14+(R14/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]P15[/TH]
[TD="align: left"]=$Q15+(R15/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]P16[/TH]
[TD="align: left"]=$Q16+(R16/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]P17[/TH]
[TD="align: left"]=$Q17+(R17/60)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]PF[/TH]
[TD="align: left"]=List!$E$2:$E$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]PFA[/TH]
[TD="align: left"]=List!#REF![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Table 2 in next post

Matt
 
Last edited:
Upvote 0
Table 2

Excel 2007
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBN

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="bgcolor: #969696, align: center"]Age Group[/TD]
[TD="bgcolor: #969696, align: center"]< 30[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]30 - 34[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]35 - 39[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]40 - 44[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]45 - 49[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]50 - 54[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]55 - 59[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]60 - 64[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Age Group[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #969696, align: center"]Points[/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]M[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Range floor[/TD]
[TD="bgcolor: #969696, align: center"]D.mins[/TD]
[TD="bgcolor: #969696, align: center"]F[/TD]
[TD="bgcolor: #969696, align: center"][/TD]
[TD="bgcolor: #969696, align: center"]Points[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #969696, align: center"]100[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]8.25[/TD]
[TD="bgcolor: #FFFF99, align: center"]08[/TD]
[TD="bgcolor: #FFFF99, align: center"]15[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]10.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]00[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]8.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]08[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]10.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]9.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]09[/TD]
[TD="bgcolor: #FFFF99, align: center"]00[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]11.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]11[/TD]
[TD="bgcolor: #FFFF99, align: center"]00[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]9.25[/TD]
[TD="bgcolor: #FFFF99, align: center"]09[/TD]
[TD="bgcolor: #FFFF99, align: center"]15[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]11.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]11[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]9.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]09[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]12.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]12[/TD]
[TD="bgcolor: #FFFF99, align: center"]00[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]10.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]00[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]12.75[/TD]
[TD="bgcolor: #FFFF99, align: center"]12[/TD]
[TD="bgcolor: #FFFF99, align: center"]45[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]10.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]13.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]13[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]10.75[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]45[/TD]
[TD="bgcolor: #FFFFFF, align: center"]0[/TD]
[TD="align: center"]13.83[/TD]
[TD="bgcolor: #FFFF99, align: center"]13[/TD]
[TD="bgcolor: #FFFF99, align: center"]50[/TD]
[TD="bgcolor: #969696, align: center"]100[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #969696, align: center"]99[/TD]
[TD="align: center"]8.27[/TD]
[TD="align: center"]8.32[/TD]
[TD="bgcolor: #FFFF99, align: center"]08[/TD]
[TD="bgcolor: #FFFF99, align: center"]19[/TD]
[TD="align: center"]10.02[/TD]
[TD="align: center"]10.10[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]06[/TD]
[TD="align: center"]8.52[/TD]
[TD="align: center"]8.58[/TD]
[TD="bgcolor: #FFFF99, align: center"]08[/TD]
[TD="bgcolor: #FFFF99, align: center"]35[/TD]
[TD="align: center"]10.52[/TD]
[TD="align: center"]10.60[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]36[/TD]
[TD="align: center"]9.02[/TD]
[TD="align: center"]9.08[/TD]
[TD="bgcolor: #FFFF99, align: center"]09[/TD]
[TD="bgcolor: #FFFF99, align: center"]05[/TD]
[TD="align: center"]11.02[/TD]
[TD="align: center"]11.10[/TD]
[TD="bgcolor: #FFFF99, align: center"]11[/TD]
[TD="bgcolor: #FFFF99, align: center"]06[/TD]
[TD="align: center"]9.27[/TD]
[TD="align: center"]9.33[/TD]
[TD="bgcolor: #FFFF99, align: center"]09[/TD]
[TD="bgcolor: #FFFF99, align: center"]20[/TD]
[TD="align: center"]11.52[/TD]
[TD="align: center"]11.60[/TD]
[TD="bgcolor: #FFFF99, align: center"]11[/TD]
[TD="bgcolor: #FFFF99, align: center"]36[/TD]
[TD="align: center"]9.52[/TD]
[TD="align: center"]9.60[/TD]
[TD="bgcolor: #FFFF99, align: center"]09[/TD]
[TD="bgcolor: #FFFF99, align: center"]36[/TD]
[TD="align: center"]12.02[/TD]
[TD="align: center"]12.10[/TD]
[TD="bgcolor: #FFFF99, align: center"]12[/TD]
[TD="bgcolor: #FFFF99, align: center"]06[/TD]
[TD="align: center"]10.02[/TD]
[TD="align: center"]10.12[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]07[/TD]
[TD="align: center"]12.77[/TD]
[TD="align: center"]12.85[/TD]
[TD="bgcolor: #FFFF99, align: center"]12[/TD]
[TD="bgcolor: #FFFF99, align: center"]51[/TD]
[TD="align: center"]10.52[/TD]
[TD="align: center"]10.62[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]37[/TD]
[TD="align: center"]13.52[/TD]
[TD="align: center"]13.60[/TD]
[TD="bgcolor: #FFFF99, align: center"]13[/TD]
[TD="bgcolor: #FFFF99, align: center"]36[/TD]
[TD="align: center"]10.77[/TD]
[TD="align: center"]10.87[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]52[/TD]
[TD="align: center"]13.85[/TD]
[TD="align: center"]13.93[/TD]
[TD="bgcolor: #FFFF99, align: center"]13[/TD]
[TD="bgcolor: #FFFF99, align: center"]56[/TD]
[TD="bgcolor: #969696, align: center"]99[/TD]

</tbody>
PFA Conv Table - 1.5 Mile Run​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C5[/TH]
[TD="align: left"]=$D5+($E5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G5[/TH]
[TD="align: left"]=$H5+($I5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]K5[/TH]
[TD="align: left"]=$L5+($M5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O5[/TH]
[TD="align: left"]=$P5+($Q5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]S5[/TH]
[TD="align: left"]=$T5+($U5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]W5[/TH]
[TD="align: left"]=$X5+($Y5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AA5[/TH]
[TD="align: left"]=$AB5+($AC5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AE5[/TH]
[TD="align: left"]=$AF5+($AG5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AI5[/TH]
[TD="align: left"]=$AJ5+($AK5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AM5[/TH]
[TD="align: left"]=$AN5+($AO5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AQ5[/TH]
[TD="align: left"]=$AR5+($AS5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AU5[/TH]
[TD="align: left"]=$AV5+($AW5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AY5[/TH]
[TD="align: left"]=$AZ5+($BA5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BC5[/TH]
[TD="align: left"]=$BD5+($BE5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BG5[/TH]
[TD="align: left"]=$BH5+($BI5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BK5[/TH]
[TD="align: left"]=$BL5+($BM5/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B6[/TH]
[TD="align: left"]=$D5+(($E5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]C6[/TH]
[TD="align: left"]=$D6+($E6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]F6[/TH]
[TD="align: left"]=$H5+(($I5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G6[/TH]
[TD="align: left"]=$H6+($I6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]J6[/TH]
[TD="align: left"]=$L5+(($M5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]K6[/TH]
[TD="align: left"]=$L6+($M6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]N6[/TH]
[TD="align: left"]=$P5+(($Q5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]O6[/TH]
[TD="align: left"]=$P6+($Q6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]R6[/TH]
[TD="align: left"]=$T5+(($U5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]S6[/TH]
[TD="align: left"]=$T6+($U6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]V6[/TH]
[TD="align: left"]=$X5+(($Y5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]W6[/TH]
[TD="align: left"]=$X6+($Y6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]Z6[/TH]
[TD="align: left"]=$AB5+(($AC5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AA6[/TH]
[TD="align: left"]=$AB6+($AC6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AD6[/TH]
[TD="align: left"]=$AF5+(($AG5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AE6[/TH]
[TD="align: left"]=$AF6+($AG6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AH6[/TH]
[TD="align: left"]=$AJ5+(($AK5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AI6[/TH]
[TD="align: left"]=$AJ6+($AK6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AL6[/TH]
[TD="align: left"]=$AN5+(($AO5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AM6[/TH]
[TD="align: left"]=$AN6+($AO6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AP6[/TH]
[TD="align: left"]=$AR5+(($AS5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AQ6[/TH]
[TD="align: left"]=$AR6+($AS6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AT6[/TH]
[TD="align: left"]=$AV5+(($AW5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AU6[/TH]
[TD="align: left"]=$AV6+($AW6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AX6[/TH]
[TD="align: left"]=$AZ5+(($BA5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AY6[/TH]
[TD="align: left"]=$AZ6+($BA6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BB6[/TH]
[TD="align: left"]=$BD5+(($BE5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BC6[/TH]
[TD="align: left"]=$BD6+($BE6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BF6[/TH]
[TD="align: left"]=$BH5+(($BI5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BG6[/TH]
[TD="align: left"]=$BH6+($BI6/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BJ6[/TH]
[TD="align: left"]=$BL5+(($BM5+1)/60)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]BK6[/TH]
[TD="align: left"]=$BL6+($BM6/60)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The table points go down to 50 anything after is zero points
 
Last edited:
Upvote 0
mattbird said:
Matty,

Table 1 is the input table and Table 2 (next post) is where it looks for the result.

Table 1

Excel 2007
DEFGHIJKLMNOPQRST

<tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: #C0C0C0, align: center"]DOB[/TD]
[TD="bgcolor: #C0C0C0, align: center"]AGE[/TD]
[TD="bgcolor: #C0C0C0, align: center"]M/F[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SQN[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Date of[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Date of Next[/TD]
[TD="bgcolor: #C0C0C0, align: center"]PRESS - UPS[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]SIT - UPS[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]2.4km TIMED RUN[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"]COY[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Assessment[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Assessment[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SCORE[/TD]
[TD="bgcolor: #C0C0C0, align: center"]POINTS[/TD]
[TD="bgcolor: #C0C0C0, align: center"]RESULT[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SCORE[/TD]
[TD="bgcolor: #C0C0C0, align: center"]POINTS[/TD]
[TD="bgcolor: #C0C0C0, align: center"]RESULT[/TD]
[TD="bgcolor: #C0C0C0, align: center"]D.MIN[/TD]
[TD="bgcolor: #C0C0C0, align: center"]MIN[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SEC[/TD]
[TD="bgcolor: #C0C0C0, align: center"]POINTS[/TD]
[TD="bgcolor: #C0C0C0, align: center"]RESULT[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]
[TD="bgcolor: #C0C0C0, align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFF99, align: center"]11/04/90[/TD]
[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFF99, align: center"]M[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]10.50[/TD]
[TD="bgcolor: #FFFF99, align: center"]10[/TD]
[TD="bgcolor: #FFFF99, align: center"]30[/TD]
[TD="bgcolor: #FFFF99, align: center"]70[/TD]
[TD="bgcolor: #FFFF99, align: center"]P[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

</tbody>
PFA​
Table 2 in next post

Matt
Click to expand...

Hi Matt,

Can you explain what result(s) you want returned?

Looking at Table 2, its layout will make looking up data more of a challenge than it needs to be. Would you be open to changing the layout?

Matty
 
Upvote 0
Matty,

The results I would like to be returned is the points in column A (Table 2) and this will depend on gender and age. Columns Q & R (Table 1) is the time the individual completed the run, column P is it converted to decimal minutes. It look for the decimal minutes in the Range floor columns (Table 2) or closest too and then returns the points from column A (Table 2).

Example:

Male: 25 yrs old runs 11min 06sec (shown in Table 1 columns Q&R, which equals 11:10 D.mins column P) and receives 62 points (looks at column B Table 2 equivalent to the time highlighted Yellow in columns D&E and returns the points from column A).

Female: 36 yrs old runs 11:06 and receives 99 points (looks at column V Table 2 equivalent to the time highlighted yellow in columns T&U and returns the points from column A).

How would the layout change?

Matt
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Google Sheets Formula Help
Replies
5
Views
376
Jewells0905
J
1
  • Question Question
Index + Match on a table: when sorting table options, I+M breaks
Replies
1
Views
920
Fluff
Fluff
T
  • Solved
Sumproduct data on different sheets and multiple criteria
Replies
3
Views
824
Fluff
Fluff
J
  • Solved
Named ranges/functions formula trouble with Google Sheets
Replies
1
Views
337
Jewells0905
J
W
  • Solved
Removing Data Validation List Items Once Selected In Another Cell
Replies
3
Views
648
James006
James006

Forum statistics

Threads
1,222,143
Messages
6,164,180
Members
451,880
Latest member
2da

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top