Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HMATCH star matching
I am using Excel 2003 (11.6355.6360) SP1 on an XP machine.
I am attempting to write a worksheet function to translate a GCSE grade to a value. (GCSE is General Certificate of Secondary Education ) The following grades and values are used Grade A* A B C D E F G U Value 58 52 46 40 34 28 22 16 0 Currently, I use a naive VBA function to do the job. (It consists of a sequence of "if param = grade then func = value end if" clauses.) On one line, I tried =HLOOKUP(GRADE, {"A", "A*, "B", "C", "D", "E", "F", "G", "U"; 52, 58, 52, 46, 40, 34, 28, 22, 16}, FALSE) That works well for expected values other than "A*" (eh star) and for unexpected values such as "What?" which returns #NA. However, A GRADE of "A*" (eh star) matches "A" and 52 is returned. A GRADE of "A~*" (eh tilde star) matches "A*" and 58 is returned. Is this expected behaviour? (British spelling.) If so, is there a function to literalize metacharacters such as "*" (star)? Is there any documentation covering the point? -- Walter Briscoe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HMATCH star matching
I may have got the thresholds wrong so adjust if necessary
=LOOKUP(GRADE,{0,16,22,28,34,40,46,52},{"U","G","F ","E","C","B","A","A*","D"}) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Walter Briscoe" wrote in message ... I am using Excel 2003 (11.6355.6360) SP1 on an XP machine. I am attempting to write a worksheet function to translate a GCSE grade to a value. (GCSE is General Certificate of Secondary Education ) The following grades and values are used Grade A* A B C D E F G U Value 58 52 46 40 34 28 22 16 0 Currently, I use a naive VBA function to do the job. (It consists of a sequence of "if param = grade then func = value end if" clauses.) On one line, I tried =HLOOKUP(GRADE, {"A", "A*, "B", "C", "D", "E", "F", "G", "U"; 52, 58, 52, 46, 40, 34, 28, 22, 16}, FALSE) That works well for expected values other than "A*" (eh star) and for unexpected values such as "What?" which returns #NA. However, A GRADE of "A*" (eh star) matches "A" and 52 is returned. A GRADE of "A~*" (eh tilde star) matches "A*" and 58 is returned. Is this expected behaviour? (British spelling.) If so, is there a function to literalize metacharacters such as "*" (star)? Is there any documentation covering the point? -- Walter Briscoe |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HMATCH star matching
In message of Tue, 28 Aug 2007
12:47:44 in microsoft.public.excel.worksheet.functions, Bob Phillips writes I may have got the thresholds wrong so adjust if necessary =LOOKUP(GRADE,{0,16,22,28,34,40,46,52},{"U","G"," F","E","C","B","A","A*","D"}) I am sorry I failed to explain myself adequately. That maps 0 to 16- onto "U", 16 to 22- onto "G", etc. I want to do it the other way round: A* maps to 58; A maps to 52; B maps to 46; etc. If I was to use LOOKUP, "H" would map to 0 and "BC" would map to 40, etc. because LOOKUP matches a value in a range to a precise value. I want to map one of a set of precise values to a precise value and other values not to match. e.g. A* 58 AB #NA A 52 B 46 BC #NA C 40 .... G 16 H #NA U 0 V #NA etc. "Walter Briscoe" wrote in message ... I am using Excel 2003 (11.6355.6360) SP1 on an XP machine. I am attempting to write a worksheet function to translate a GCSE grade to a value. (GCSE is General Certificate of Secondary Education ) The following grades and values are used Grade A* A B C D E F G U Value 58 52 46 40 34 28 22 16 0 Currently, I use a naive VBA function to do the job. (It consists of a sequence of "if param = grade then func = value end if" clauses.) On one line, I tried =HLOOKUP(GRADE, {"A", "A*, "B", "C", "D", "E", "F", "G", "U"; 52, 58, 52, 46, 40, 34, 28, 22, 16}, That should have been 52, 58, 46, 40, 34, 28, 22, 16, 0}, FALSE) -- Walter Briscoe |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HMATCH star matching
Try either this
=VLOOKUP(Grade,{"A*",58;"A",52;"B",46;"C",40;"D",3 4;"E",28;"F",22;"G",16;"U",0},2,0) or this =HLOOKUP(Grade,{"A*","A","B","C","D","E","F","G"," U";58,52,46,40,34,28,22,16,0},2,0) -- Regards, Peo Sjoblom "Walter Briscoe" wrote in message ... In message of Tue, 28 Aug 2007 12:47:44 in microsoft.public.excel.worksheet.functions, Bob Phillips writes I may have got the thresholds wrong so adjust if necessary =LOOKUP(GRADE,{0,16,22,28,34,40,46,52},{"U","G", "F","E","C","B","A","A*","D"}) I am sorry I failed to explain myself adequately. That maps 0 to 16- onto "U", 16 to 22- onto "G", etc. I want to do it the other way round: A* maps to 58; A maps to 52; B maps to 46; etc. If I was to use LOOKUP, "H" would map to 0 and "BC" would map to 40, etc. because LOOKUP matches a value in a range to a precise value. I want to map one of a set of precise values to a precise value and other values not to match. e.g. A* 58 AB #NA A 52 B 46 BC #NA C 40 ... G 16 H #NA U 0 V #NA etc. "Walter Briscoe" wrote in message ... I am using Excel 2003 (11.6355.6360) SP1 on an XP machine. I am attempting to write a worksheet function to translate a GCSE grade to a value. (GCSE is General Certificate of Secondary Education ) The following grades and values are used Grade A* A B C D E F G U Value 58 52 46 40 34 28 22 16 0 Currently, I use a naive VBA function to do the job. (It consists of a sequence of "if param = grade then func = value end if" clauses.) On one line, I tried =HLOOKUP(GRADE, {"A", "A*, "B", "C", "D", "E", "F", "G", "U"; 52, 58, 52, 46, 40, 34, 28, 22, 16}, That should have been 52, 58, 46, 40, 34, 28, 22, 16, 0}, FALSE) -- Walter Briscoe |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HMATCH star matching
In message of Tue, 28 Aug 2007
09:00:30 in microsoft.public.excel.worksheet.functions, Peo Sjoblom writes Try either this =VLOOKUP(Grade,{"A*",58;"A",52;"B",46;"C",40;"D", 34;"E",28;"F",22;"G",16 ;"U",0},2,0) or this =HLOOKUP(Grade,{"A*","A","B","C","D","E","F","G", "U";58,52,46,40,34,28,2 2,16,0},2,0) Thank you! Both work. I had started with LOOKUP which needs sorted input and "A" < "A*". -- Walter Briscoe |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HMATCH star matching
"Peo Sjoblom" wrote...
Try either this =VLOOKUP(Grade,{"A*",58;"A",52;"B",46;"C",40;"D", 34;"E",28; "F",22;"G",16;"U",0},2,0) or this =HLOOKUP(Grade,{"A*","A","B","C","D","E","F","G", "U"; 58,52,46,40,34,28,22,16,0},2,0) .... Picky: if Grade were A? both formulas would return 58; if Grade were A~ both formulas would return 52; if Grade were just * both formulas would return 58; if Grade were just ? both formulas would return 52. If that doesn't matter to the OP, fine. However, if the OP wants to kick out anything except the actual valid grades, then make the 1st argument to the lookup functions SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Grade,"~","~~")," *","~*"),"?","~?") or use the array formula =INDEX({58,52,46,40,34,28,22,16,0}, MATCH(TRUE,EXACT(Grade,{"A*","A","B","C","D","E"," F","G","U"}),0)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HMATCH star matching
In message .com of
Wed, 29 Aug 2007 06:43:40 in microsoft.public.excel.worksheet.functions, Harlan Grove writes "Peo Sjoblom" wrote... Try either this =VLOOKUP(Grade,{"A*",58;"A",52;"B",46;"C",40;"D" ,34;"E",28; "F",22;"G",16;"U",0},2,0) or this =HLOOKUP(Grade,{"A*","A","B","C","D","E","F","G" ,"U"; 58,52,46,40,34,28,22,16,0},2,0) ... Picky: if Grade were A? both formulas would return 58; if Grade were A~ both formulas would return 52; if Grade were just * both formulas would return 58; if Grade were just ? both formulas would return 52. If that doesn't matter to the OP, fine. However, if the OP wants to kick out anything except the actual valid grades, then make the 1st argument to the lookup functions SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Grade,"~","~~"), "*","~*"),"?","~?") Thank you for being picky. I was so relieved that I could correctly translate "A*" and "A" that I did not do full testing. I tried =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Grade,"~ ","~~"),"*","~*"),"?","~?"), ... which works well apart from failure to distinguish "B" which is valid and "b" which is not. or use the array formula =INDEX({58,52,46,40,34,28,22,16,0}, MATCH(TRUE,EXACT(Grade,{"A*","A","B","C","D","E", "F","G","U"}),0)) That returns the desired result {58 ,52 ,#NA ,#NA} for {"A*","A","A?","a"} and seems the best alternative to date. Thank you very much. I had missed the relevant documentation in the VLOOKUP help page. -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
my mouse pointer is a star I want a plus | New Users to Excel | |||
Bob- you are a star! | Excel Discussion (Misc queries) | |||
star | Excel Discussion (Misc queries) | |||
what does the star (*) indicate in an excel spreadsheet | Excel Discussion (Misc queries) |