|
Index match question
=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) , 1+MATCH(C1,A1:A10,1))) I put my table in A1:B10, the value to lookup in C1. I found this formula in another post. I entered some data into these cells to see if it worked and it worked perfectly. The credit goes to Dave Peterson, not me. I am trying to modify this formula to look up a value that is dependent on multiple criteria. On the worksheet that contains the formula, a person's sex is in cell C4, age in cell D4, and run time in cell I4. It will need to look in a range (cell A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run. In the following sample of the "2MI DATA" below, "AGE" is in cell a1. AGE 17-21 17-21 22-26 22-26 27-31 27-31 Time M F M F M F 12:48 100 100 100 100 100 100 12:54 100 100 100 100 100 100 13:00 100 100 100 100 100 100 13:06 99 100 99 100 100 100 13:12 97 100 98 100 100 100 13:18 96 100 97 100 100 100 13:24 94 100 96 100 99 100 13:30 93 100 94 100 98 100 Can the match/index formula above be modified to do the following: For a 20 year old male who runs 13:26, the formula should return a value of 93. Thanks for any help you can provide. |
Index match question
On May 29, 10:27 pm, SGT Buckeye wrote:
=INDEX(B1:B10, IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) , 1+MATCH(C1,A1:A10,1))) I put my table in A1:B10, the value to lookup in C1. I found this formula in another post. I entered some data into these cells to see if it worked and it worked perfectly. The credit goes to Dave Peterson, not me. I am trying to modify this formula to look up a value that is dependent onmultiplecriteria. On the worksheet that contains the formula, a person's sex is in cell C4, age in cell D4, and run time in cell I4. It will need to look in a range (cell A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run. In the following sample of the "2MI DATA" below, "AGE" is in cell a1. AGE 17-21 17-21 22-26 22-26 27-31 27-31 Time M F M F M F 12:48 100 100 100 100 100 100 12:54 100 100 100 100 100 100 13:00 100 100 100 100 100 100 13:06 99 100 99 100 100 100 13:12 97 100 98 100 100 100 13:18 96 100 97 100 100 100 13:24 94 100 96 100 99 100 13:30 93 100 94 100 98 100 Can thematch/indexformula above be modified to do the following: For a 20 year old male who runs 13:26, the formula should return a value of 93. Thanks for any help you can provide. Just to clarify, if a person's run time falls between two of the run times listed in column A, the formula should return the lower value. I am using this for my Army unit to compile test scores for the Army physical fitness test. Thanks again for the help. |
Index match question
----------------------------------------------------------------------- "SGT Buckeye" wrote in message oups.com... On May 29, 10:27 pm, SGT Buckeye wrote: =INDEX(B1:B10, IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) , 1+MATCH(C1,A1:A10,1))) I put my table in A1:B10, the value to lookup in C1. I found this formula in another post. I entered some data into these cells to see if it worked and it worked perfectly. The credit goes to Dave Peterson, not me. I am trying to modify this formula to look up a value that is dependent onmultiplecriteria. On the worksheet that contains the formula, a person's sex is in cell C4, age in cell D4, and run time in cell I4. It will need to look in a range (cell A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run. In the following sample of the "2MI DATA" below, "AGE" is in cell a1. AGE 17-21 17-21 22-26 22-26 27-31 27-31 Time M F M F M F 12:48 100 100 100 100 100 100 12:54 100 100 100 100 100 100 13:00 100 100 100 100 100 100 13:06 99 100 99 100 100 100 13:12 97 100 98 100 100 100 13:18 96 100 97 100 100 100 13:24 94 100 96 100 99 100 13:30 93 100 94 100 98 100 Can thematch/indexformula above be modified to do the following: For a 20 year old male who runs 13:26, the formula should return a value of 93. Thanks for any help you can provide. Just to clarify, if a person's run time falls between two of the run times listed in column A, the formula should return the lower value. I am using this for my Army unit to compile test scores for the Army physical fitness test. Thanks again for the help. Sgt Buckeye, First, I would simplify this by separating your M and F tables. Also, remove the upper age range value (e.g.: instead of 17-21, put only 17); that is, list only the lower of each. In sheet "2MI DATA", put the M table, starting in A1, as follows: Male 17 22 27 32 12:48 100 100 100 12:54 100 100 100 13:00 100 100 100 13:06 99 99 100 13:12 97 98 100 13:18 96 97 100 13:24 94 96 99 13:30 93 94 98 The "Male" in A1 is just a heading, for our edification, and not used by the formulas. Since there's no score for age 32 and up, i've left that column blank. You could put scores in it, and any age 32 and above would get that score. Now, for M only, you would use this formula: =VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1) It's a VLOOKUP that uses a MATCH to figure out which column to tell the VLOOKUP to use (its third argument). I've put the F table in starting in G1 of sheet 2MI DATA: Female 17 22 27 32 12:48 100 100 100 12:54 100 100 100 13:00 100 100 100 13:06 100 100 100 13:12 100 100 100 13:18 100 100 100 13:24 100 100 100 13:30 100 100 100 Now to send it to the M or F table, wrap the above, and a similar one adjusted for the F table, in an IF statement: =IF(C4="M",VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1),VLOOKUP(I4,'2MI DATA'!G3:K10,MATCH(D4,'2MI DATA'!H2:K2,1)+1)) This presumes that if M isn't in C4, it's F -- it doesn't actually look at it. You should use Data - Validation on that cell allowing only M or F, or an entry other than M or F might give you incorrect results, like if someone puts "Don't ask, don't tell" in the cell. Now get an appointment, in advance (cause you're gonna need it), with a psychiatrist, yoga instructor, or get a big bottle of downers, because this is not a good thing to try to maintain or debug. It's too messy, and will have you barking within hours if it ever breaks or has to be changed. I made it by pasting separate formulas together, dealing with smaller pieces. I'd recommend splitting it up into separate pieces (like the M only formula above), then hiding the those columns or rows of those cells. It makes maintenance MUCH better. Concise is for geeks. Verbose is better for maintainability (changing stuff later) and reliability (less chance of an error in the formulas). Post back if you're interested. Also, it'd be a bit better (and better design) to put the M and F tables in separate sheets, like "2MI DATA M", and "2MI DATA F". That way, the tables would be in the same places of their respective sheets. This is not thoroughly tested. I've left that to you. But I think it's pretty close to what you need. -- Earl Kiosterud www.smokeylake.com |
Index match question
"Earl Kiosterud" wrote in message ... ----------------------------------------------------------------------- "SGT Buckeye" wrote in message oups.com... On May 29, 10:27 pm, SGT Buckeye wrote: =INDEX(B1:B10, IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) , 1+MATCH(C1,A1:A10,1))) I put my table in A1:B10, the value to lookup in C1. I found this formula in another post. I entered some data into these cells to see if it worked and it worked perfectly. The credit goes to Dave Peterson, not me. I am trying to modify this formula to look up a value that is dependent onmultiplecriteria. On the worksheet that contains the formula, a person's sex is in cell C4, age in cell D4, and run time in cell I4. It will need to look in a range (cell A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run. In the following sample of the "2MI DATA" below, "AGE" is in cell a1. AGE 17-21 17-21 22-26 22-26 27-31 27-31 Time M F M F M F 12:48 100 100 100 100 100 100 12:54 100 100 100 100 100 100 13:00 100 100 100 100 100 100 13:06 99 100 99 100 100 100 13:12 97 100 98 100 100 100 13:18 96 100 97 100 100 100 13:24 94 100 96 100 99 100 13:30 93 100 94 100 98 100 Can thematch/indexformula above be modified to do the following: For a 20 year old male who runs 13:26, the formula should return a value of 93. Thanks for any help you can provide. Just to clarify, if a person's run time falls between two of the run times listed in column A, the formula should return the lower value. I am using this for my Army unit to compile test scores for the Army physical fitness test. Thanks again for the help. Sgt Buckeye, First, I would simplify this by separating your M and F tables. Also, remove the upper age range value (e.g.: instead of 17-21, put only 17); that is, list only the lower of each. In sheet "2MI DATA", put the M table, starting in A1, as follows: A B C D E 1 Male 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 99 99 100 7 13:12 97 98 100 8 13:18 96 97 100 9 13:24 94 96 99 10 13:30 93 94 98 The "Male" in A1 is just a heading, for our edification, and not used by the formulas. Since there's no score for age 32 and up, i've left that column blank. You could put scores in it, and any age 32 and above would get that score. Now, for M only, you would use this formula: =VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1) It's a VLOOKUP that uses a MATCH to figure out which column to tell the VLOOKUP to use (its third argument). I've put the F table in starting in G1 of sheet 2MI DATA: G H I J K 1 Female 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 100 100 100 7 13:12 100 100 100 8 13:18 100 100 100 9 13:24 100 100 100 10 13:30 100 100 100 Now to send it to the M or F table, wrap the above, and a similar one adjusted for the F table, in an IF statement: =IF(C4="M",VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1),VLOOKUP(I4,'2MI DATA'!G3:K10,MATCH(D4,'2MI DATA'!H2:K2,1)+1)) This presumes that if M isn't in C4, it's F -- it doesn't actually look at it. You should use Data - Validation on that cell allowing only M or F, or an entry other than M or F might give you incorrect results, like if someone puts "Don't ask, don't tell" in the cell. Now get an appointment, in advance (cause you're gonna need it), with a psychiatrist, yoga instructor, or get a big bottle of downers, because this is not a good thing to try to maintain or debug. It's too messy, and will have you barking within hours if it ever breaks or has to be changed. I made it by pasting separate formulas together, dealing with smaller pieces. I'd recommend splitting it up into separate pieces (like the M only formula above), then hiding the those columns or rows of those cells. It makes maintenance MUCH better. Concise is for geeks. Verbose is better for maintainability (changing stuff later) and reliability (less chance of an error in the formulas). Post back if you're interested. Also, it'd be a bit better (and better design) to put the M and F tables in separate sheets, like "2MI DATA M", and "2MI DATA F". That way, the tables would be in the same places of their respective sheets. This is not thoroughly tested. I've left that to you. But I think it's pretty close to what you need. -- Earl Kiosterud www.smokeylake.com Buckeye, The stuff I pasted into the post, the tables, got messed up, as I thought it might. It's the spacing. See my quoted post above, where I've cleaned it up. If your newsreader isn't using a non-proportional font (like Courier), it's still going to be a mess. -- Earl Kiosterud www.smokeylake.com |
Index match question
I too would use separate tables. However, since the requirement is if there
is not an exact time match use the next higher time. That's how the OP came up with this: For a 20 year old male who runs 13:26, the formula should return a value of 93. In order to do this the tables need to be sorted descending. Also, all the Female values seem to be the same, 100 (dummy data?). If that's the real data then that really simplifies things! You only need to lookup for Males. Using named ranges (for brevity) one formula can incorporate both genders. Too much to explain in writing but here's a sample file. I've used 2 tables but like I mentioned above, if the Female data is the same we can simplify this even more. Sample file Tables.xls 14kb http://cjoint.com/?fEihEtiLCw Biff "Earl Kiosterud" wrote in message ... "Earl Kiosterud" wrote in message ... ----------------------------------------------------------------------- "SGT Buckeye" wrote in message oups.com... On May 29, 10:27 pm, SGT Buckeye wrote: =INDEX(B1:B10, IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) , 1+MATCH(C1,A1:A10,1))) I put my table in A1:B10, the value to lookup in C1. I found this formula in another post. I entered some data into these cells to see if it worked and it worked perfectly. The credit goes to Dave Peterson, not me. I am trying to modify this formula to look up a value that is dependent onmultiplecriteria. On the worksheet that contains the formula, a person's sex is in cell C4, age in cell D4, and run time in cell I4. It will need to look in a range (cell A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run. In the following sample of the "2MI DATA" below, "AGE" is in cell a1. AGE 17-21 17-21 22-26 22-26 27-31 27-31 Time M F M F M F 12:48 100 100 100 100 100 100 12:54 100 100 100 100 100 100 13:00 100 100 100 100 100 100 13:06 99 100 99 100 100 100 13:12 97 100 98 100 100 100 13:18 96 100 97 100 100 100 13:24 94 100 96 100 99 100 13:30 93 100 94 100 98 100 Can thematch/indexformula above be modified to do the following: For a 20 year old male who runs 13:26, the formula should return a value of 93. Thanks for any help you can provide. Just to clarify, if a person's run time falls between two of the run times listed in column A, the formula should return the lower value. I am using this for my Army unit to compile test scores for the Army physical fitness test. Thanks again for the help. Sgt Buckeye, First, I would simplify this by separating your M and F tables. Also, remove the upper age range value (e.g.: instead of 17-21, put only 17); that is, list only the lower of each. In sheet "2MI DATA", put the M table, starting in A1, as follows: A B C D E 1 Male 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 99 99 100 7 13:12 97 98 100 8 13:18 96 97 100 9 13:24 94 96 99 10 13:30 93 94 98 The "Male" in A1 is just a heading, for our edification, and not used by the formulas. Since there's no score for age 32 and up, i've left that column blank. You could put scores in it, and any age 32 and above would get that score. Now, for M only, you would use this formula: =VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1) It's a VLOOKUP that uses a MATCH to figure out which column to tell the VLOOKUP to use (its third argument). I've put the F table in starting in G1 of sheet 2MI DATA: G H I J K 1 Female 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 100 100 100 7 13:12 100 100 100 8 13:18 100 100 100 9 13:24 100 100 100 10 13:30 100 100 100 Now to send it to the M or F table, wrap the above, and a similar one adjusted for the F table, in an IF statement: =IF(C4="M",VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1),VLOOKUP(I4,'2MI DATA'!G3:K10,MATCH(D4,'2MI DATA'!H2:K2,1)+1)) This presumes that if M isn't in C4, it's F -- it doesn't actually look at it. You should use Data - Validation on that cell allowing only M or F, or an entry other than M or F might give you incorrect results, like if someone puts "Don't ask, don't tell" in the cell. Now get an appointment, in advance (cause you're gonna need it), with a psychiatrist, yoga instructor, or get a big bottle of downers, because this is not a good thing to try to maintain or debug. It's too messy, and will have you barking within hours if it ever breaks or has to be changed. I made it by pasting separate formulas together, dealing with smaller pieces. I'd recommend splitting it up into separate pieces (like the M only formula above), then hiding the those columns or rows of those cells. It makes maintenance MUCH better. Concise is for geeks. Verbose is better for maintainability (changing stuff later) and reliability (less chance of an error in the formulas). Post back if you're interested. Also, it'd be a bit better (and better design) to put the M and F tables in separate sheets, like "2MI DATA M", and "2MI DATA F". That way, the tables would be in the same places of their respective sheets. This is not thoroughly tested. I've left that to you. But I think it's pretty close to what you need. -- Earl Kiosterud www.smokeylake.com Buckeye, The stuff I pasted into the post, the tables, got messed up, as I thought it might. It's the spacing. See my quoted post above, where I've cleaned it up. If your newsreader isn't using a non-proportional font (like Courier), it's still going to be a mess. -- Earl Kiosterud www.smokeylake.com |
Index match question
On May 30, 2:13 am, "T. Valko" wrote:
I too would use separate tables. However, since the requirement is if there is not an exact time match use the next higher time. That's how the OP came up with this: For a 20 year old male who runs 13:26, the formula should return a value of 93. In order to do this the tables need to be sorted descending. Also, all the Female values seem to be the same, 100 (dummy data?). If that's the real data then that really simplifies things! You only need to lookup for Males. Using named ranges (for brevity) one formula can incorporate both genders. Too much to explain in writing but here's a sample file. I've used 2 tables but like I mentioned above, if the Female data is the same we can simplify this even more. Sample file Tables.xls 14kb http://cjoint.com/?fEihEtiLCw Biff "Earl Kiosterud" wrote in message ... "Earl Kiosterud" wrote in message ... ----------------------------------------------------------------------- "SGT Buckeye" wrote in message groups.com... On May 29, 10:27 pm, SGT Buckeye wrote: =INDEX(B1:B10, IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) , 1+MATCH(C1,A1:A10,1))) I put my table in A1:B10, the value to lookup in C1. I found this formula in another post. I entered some data into these cells to see if it worked and it worked perfectly. The credit goes to Dave Peterson, not me. I am trying to modify this formula to look up a value that is dependent onmultiplecriteria. On the worksheet that contains the formula, a person's sex is in cell C4, age in cell D4, and run time in cell I4. It will need to look in a range (cell A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run. In the following sample of the "2MI DATA" below, "AGE" is in cell a1. AGE 17-21 17-21 22-26 22-26 27-31 27-31 Time M F M F M F 12:48 100 100 100 100 100 100 12:54 100 100 100 100 100 100 13:00 100 100 100 100 100 100 13:06 99 100 99 100 100 100 13:12 97 100 98 100 100 100 13:18 96 100 97 100 100 100 13:24 94 100 96 100 99 100 13:30 93 100 94 100 98 100 Can thematch/indexformula above be modified to do the following: For a 20 year old male who runs 13:26, the formula should return a value of 93. Thanks for any help you can provide. Just to clarify, if a person's run time falls between two of the run times listed in column A, the formula should return the lower value. I am using this for my Army unit to compile test scores for the Army physical fitness test. Thanks again for the help. Sgt Buckeye, First, I would simplify this by separating your M and F tables. Also, remove the upper age range value (e.g.: instead of 17-21, put only 17); that is, list only the lower of each. In sheet "2MI DATA", put the M table, starting in A1, as follows: A B C D E 1 Male 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 99 99 100 7 13:12 97 98 100 8 13:18 96 97 100 9 13:24 94 96 99 10 13:30 93 94 98 The "Male" in A1 is just a heading, for our edification, and not used by the formulas. Since there's no score for age 32 and up, i've left that column blank. You could put scores in it, and any age 32 and above would get that score. Now, for M only, you would use this formula: =VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1) It's a VLOOKUP that uses a MATCH to figure out which column to tell the VLOOKUP to use (its third argument). I've put the F table in starting in G1 of sheet 2MI DATA: G H I J K 1 Female 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 100 100 100 7 13:12 100 100 100 8 13:18 100 100 100 9 13:24 100 100 100 10 13:30 100 100 100 Now to send it to the M or F table, wrap the above, and a similar one adjusted for the F table, in an IF statement: =IF(C4="M",VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1),VLOOKUP(I4,'2MI DATA'!G3:K10,MATCH(D4,'2MI DATA'!H2:K2,1)+1)) This presumes that if M isn't in C4, it's F -- it doesn't actually look at it. You should use Data - Validation on that cell allowing only M or F, or an entry other than M or F might give you incorrect results, like if someone puts "Don't ask, don't tell" in the cell. Now get an appointment, in advance (cause you're gonna need it), with a psychiatrist, yoga instructor, or get a big bottle of downers, because this is not a good thing to try to maintain or debug. It's too messy, and will have you barking within hours if it ever breaks or has to be changed. I made it by pasting separate formulas together, dealing with smaller pieces. I'd recommend splitting it up into separate pieces (like the M only formula above), then hiding the those columns or rows of those cells. It makes maintenance MUCH better. Concise is for geeks. Verbose is better for maintainability (changing stuff later) and reliability (less chance of an error in the formulas). Post back if you're interested. Also, it'd be a bit better (and better design) to put the M and F tables in separate sheets, like "2MI DATA M", and "2MI DATA F". That way, the tables would be in the same places of their respective sheets. This is not thoroughly tested. I've left that to you. But I think it's pretty close to what you need. -- Earl Kiosterud www.smokeylake.com Buckeye, The stuff I pasted into the post, the tables, got messed up, as I thought it might. It's the spacing. See my quoted post above, where I've cleaned it up. If your newsreader isn't using a non-proportional font (like Courier), it's still going to be a mess. -- Earl Kiosterud www.smokeylake.com- Hide quoted text - - Show quoted text - Thank you to everyone who offered suggestions but I was able to figure it out. I modified the worksheet that contained the run time and points earned data to include headers in the pattern M17, F17, M18, F18. I then sorted the information in descending order by run time (see table below). Time M17 F17 M18 F18 26:36 0 0 0 0 26:30 0 0 0 0 26:24 0 0 0 0 26:18 0 0 0 0 I then used the following formula using a double lookup: =IF(ISBLANK($J4)," ",IF($J4=RUN!A2,0,IF($J4<=RUN!A138,100,OFFSET(RUN ! $A$1,MATCH($J4,RUN!$A$2:$A$140,-1),MATCH($E4,RUN!$B$1:$CO$1,0))))) I have been working on this off and on for two weeks under various topic titels. Again, many thanks to everyone who has offered help trying to figure this one out. |
Index match question
"SGT Buckeye" wrote in message ups.com... On May 30, 2:13 am, "T. Valko" wrote: I too would use separate tables. However, since the requirement is if there is not an exact time match use the next higher time. That's how the OP came up with this: For a 20 year old male who runs 13:26, the formula should return a value of 93. In order to do this the tables need to be sorted descending. Also, all the Female values seem to be the same, 100 (dummy data?). If that's the real data then that really simplifies things! You only need to lookup for Males. Using named ranges (for brevity) one formula can incorporate both genders. Too much to explain in writing but here's a sample file. I've used 2 tables but like I mentioned above, if the Female data is the same we can simplify this even more. Sample file Tables.xls 14kb http://cjoint.com/?fEihEtiLCw Biff "Earl Kiosterud" wrote in message ... "Earl Kiosterud" wrote in message ... ----------------------------------------------------------------------- "SGT Buckeye" wrote in message groups.com... On May 29, 10:27 pm, SGT Buckeye wrote: =INDEX(B1:B10, IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) , 1+MATCH(C1,A1:A10,1))) I put my table in A1:B10, the value to lookup in C1. I found this formula in another post. I entered some data into these cells to see if it worked and it worked perfectly. The credit goes to Dave Peterson, not me. I am trying to modify this formula to look up a value that is dependent onmultiplecriteria. On the worksheet that contains the formula, a person's sex is in cell C4, age in cell D4, and run time in cell I4. It will need to look in a range (cell A1:U141) called "2MI DATA" on a separate worksheet called 2 Mile Run. In the following sample of the "2MI DATA" below, "AGE" is in cell a1. AGE 17-21 17-21 22-26 22-26 27-31 27-31 Time M F M F M F 12:48 100 100 100 100 100 100 12:54 100 100 100 100 100 100 13:00 100 100 100 100 100 100 13:06 99 100 99 100 100 100 13:12 97 100 98 100 100 100 13:18 96 100 97 100 100 100 13:24 94 100 96 100 99 100 13:30 93 100 94 100 98 100 Can thematch/indexformula above be modified to do the following: For a 20 year old male who runs 13:26, the formula should return a value of 93. Thanks for any help you can provide. Just to clarify, if a person's run time falls between two of the run times listed in column A, the formula should return the lower value. I am using this for my Army unit to compile test scores for the Army physical fitness test. Thanks again for the help. Sgt Buckeye, First, I would simplify this by separating your M and F tables. Also, remove the upper age range value (e.g.: instead of 17-21, put only 17); that is, list only the lower of each. In sheet "2MI DATA", put the M table, starting in A1, as follows: A B C D E 1 Male 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 99 99 100 7 13:12 97 98 100 8 13:18 96 97 100 9 13:24 94 96 99 10 13:30 93 94 98 The "Male" in A1 is just a heading, for our edification, and not used by the formulas. Since there's no score for age 32 and up, i've left that column blank. You could put scores in it, and any age 32 and above would get that score. Now, for M only, you would use this formula: =VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1) It's a VLOOKUP that uses a MATCH to figure out which column to tell the VLOOKUP to use (its third argument). I've put the F table in starting in G1 of sheet 2MI DATA: G H I J K 1 Female 2 17 22 27 32 3 12:48 100 100 100 4 12:54 100 100 100 5 13:00 100 100 100 6 13:06 100 100 100 7 13:12 100 100 100 8 13:18 100 100 100 9 13:24 100 100 100 10 13:30 100 100 100 Now to send it to the M or F table, wrap the above, and a similar one adjusted for the F table, in an IF statement: =IF(C4="M",VLOOKUP(I4,'2MI DATA'!A3:E10,MATCH(D4,'2MI DATA'!B2:E2,1)+1),VLOOKUP(I4,'2MI DATA'!G3:K10,MATCH(D4,'2MI DATA'!H2:K2,1)+1)) This presumes that if M isn't in C4, it's F -- it doesn't actually look at it. You should use Data - Validation on that cell allowing only M or F, or an entry other than M or F might give you incorrect results, like if someone puts "Don't ask, don't tell" in the cell. Now get an appointment, in advance (cause you're gonna need it), with a psychiatrist, yoga instructor, or get a big bottle of downers, because this is not a good thing to try to maintain or debug. It's too messy, and will have you barking within hours if it ever breaks or has to be changed. I made it by pasting separate formulas together, dealing with smaller pieces. I'd recommend splitting it up into separate pieces (like the M only formula above), then hiding the those columns or rows of those cells. It makes maintenance MUCH better. Concise is for geeks. Verbose is better for maintainability (changing stuff later) and reliability (less chance of an error in the formulas). Post back if you're interested. Also, it'd be a bit better (and better design) to put the M and F tables in separate sheets, like "2MI DATA M", and "2MI DATA F". That way, the tables would be in the same places of their respective sheets. This is not thoroughly tested. I've left that to you. But I think it's pretty close to what you need. -- Earl Kiosterud www.smokeylake.com Buckeye, The stuff I pasted into the post, the tables, got messed up, as I thought it might. It's the spacing. See my quoted post above, where I've cleaned it up. If your newsreader isn't using a non-proportional font (like Courier), it's still going to be a mess. -- Earl Kiosterud www.smokeylake.com- Hide quoted text - - Show quoted text - Thank you to everyone who offered suggestions but I was able to figure it out. I modified the worksheet that contained the run time and points earned data to include headers in the pattern M17, F17, M18, F18. I then sorted the information in descending order by run time (see table below). Time M17 F17 M18 F18 26:36 0 0 0 0 26:30 0 0 0 0 26:24 0 0 0 0 26:18 0 0 0 0 I then used the following formula using a double lookup: =IF(ISBLANK($J4)," ",IF($J4=RUN!A2,0,IF($J4<=RUN!A138,100,OFFSET(RUN ! $A$1,MATCH($J4,RUN!$A$2:$A$140,-1),MATCH($E4,RUN!$B$1:$CO$1,0))))) I have been working on this off and on for two weeks under various topic titels. Again, many thanks to everyone who has offered help trying to figure this one out. Glad you got it worked out. I remember those 2m runs. They were a piece of cake! I always finished 1st or 2nd. Biff |
| All times are GMT +1. The time now is 03:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com