Lookup table for grade calculation

[TheRealOrion]

I'm trying to make a sheet that will convert letter grades to their numbers and I'm getting garbled results. Sometimes, I end up with seemingly random numbers. Sometimes I just get ###. I've attached a sample. I'm sure it's something dead simple, but I'm not seeing the problem. Help would be much appreciated. I just want the system to see "C+" for example, and return "7."

[JohnV]

I changed your formula to:
=VLOOKUP(A1;$E$1:$F$14;2;0)
and then did Tools > Options > Calc > Calculate and unchecked Regular Expressions.

In this case the plus sign is being seen as a Regular Expression.

Using A#, etc., will get around this problem.

[keme]

The LOOKUP() function only works with sorted-key lists. Although you see the list as sorted (according to grading level), it's unsorted as far as Calc is concerned. With text values, both A+ and A- come after A. The other lookup functions (VLOOKUP, HLOOKUP and MATCH) can do a lookup on unsorted lists by specifying the fourth (optional) parameter of zero, like JohnV suggested. That option is not available for the LOOKUP() function.

Another approach that works is to sort your lookup table. Select E1:F14 and do Data - Sort, and your formula will work, as soon as the use of regex in formulas is disabled (see JohnV's post).

[acknak]

I changed your formula... and then did Tools > Options > Calc > Calculate and unchecked Regular Expressions.

Another approach..., and your formula will work, as soon as the use of regex in formulas is disabled (see JohnV's post).

Good advice, and an unfortunate decision by Calc's designers to make the lookup functions accept regular expressions in the lookup value, and no way to disable it from within the formula.

However, I get very nervous when a sheet requires a non-default configuration in order to produce correct answers. If someone opened your sheet without having turned off regular expressions, they would see wrong answers with no warning or hint that something was wrong.

The reverse is also true: If you disable the regular expressions option, then other sheets, that require regular expressions, may fail silently. You have to remember to change the setting both ways--a very bad situation, IMO. No--the calculation options are stored in the document and restored when the document is opened; this problem doesn't happen. See below.

One possibility is to "quote" the special characters in the data, so that if regular expressions are enabled, the lookup works correctly. This happens to be easy for your data, since the "+" is the only special character that needs to be quoted: =VLOOKUP(SUBSTITUTE(A1;"+";"\+");$E$1:$F$14;2;0) (typo fixed; see below)

That still depends on regexps being enabled, but if they are turned off, the sheet will display error messages for any grade that contains a "+", so at least there is a hint that something is wrong.

Some kind of note in the sheet, explaining about the regexp option, might be a good idea as well.

[JohnV]

acknak,

I totally agree with the problem inherent in having regular expressions turned off.

Yours is is an excellent solution but contains a typo, i.e., 1 instead of 2:
=VLOOKUP(SUBSTITUTE(A1;"+";"\+");$E$1:$F$14;1;0)
should be
=VLOOKUP(SUBSTITUTE(A1;"+";"\+");$E$1:$F$14;2;0)

[Villeroy]

All options on that page (Calc>Calculation) are stored in the document, so we can control them for each document independently from user preferences. I stored my default template for Calc has all check boxes turned off (no regexes, matching parts of strings case insensitively).

[acknak]

Ok, ok. I was thinking we'd been through this before; I even checked the option by turning it off and starting a new sheet. The option was still off in the new sheet, so I assumed it was an application setting, not a document setting. However, I see now that you're right: The setting is stored in the document and it is restored when the document is loaded, so the problem I described does not exist.

Yours is is an excellent solution but contains a typo, i.e., 1 instead of 2:...

Ack! Good catch. I was even thinking "2" when I typed it.

Maybe I should just delete that post

[TheRealOrion]

Thanks for the advice! I sorted my key and then used VLookup with the switches, and it works now (not that I really understand how it works, but that's okay).

I have a second question, directly related, so I'll attach it here. To calculate a semester's worth of grades, I have to average all the assignments, but I also weight the assignments. So someone might get a B on something worth 10% of the semester's grade, and a C- on something worth 30%, and I have to find the average. I achieve the average by taking the numerical value (from 0 to 12) and multiplying by the percentage value of the assignment, then adding up those results. I thus end up with a grade out of 12. Now my question is, how do I get Calc to take a grade that could be any number between 0 and 12, up to 2 decimal places, and spit back a letter grade? For example, if the total of a student's grade is 9.8, I want the system to spit back "B+" because a B+ is worth 10, and 9.8 rounded up is 10.

My only idea is to set the cells that have the final numerical grade to not show the decimal places, which will automatically cause Calc to round them up, and then I can do another lookup table. Will that work? Help an English teacher out, if you can.

[Villeroy]

Thanks for the advice! I sorted my key and then used VLookup with the switches, and it works now (not that I really understand how it works, but that's okay).

All spreadsheets, from the very first one http://www.danbricklin.com/history/vcexecutable.htm, are designed to lookup exactly the other way round. They use to lookup a distinct category from a scale of numbers.
In your original document, sort E1:F14 by the numbers in F, then put some numeric search values in A, a negative, a big one (1000) and some fractions such as 3.4 and 1.9 . Then use
B1 =LOOKUP(A1;$F$1:$F$14;$E$1:$E$14)

LOOKUP is designed to match at the last position on an ascending scale where the search value is greater or equal, so you get results for all possible search values except the negative ones where the search value is already smaller than the first possible match.
By default MATCH, VLOOKUP and HLOOKUP match in the exact same way, but they provide an additional switch to lookup exact values in unordered lists rather than categories in ordered scales.
MATCH_0_1.ods demonstrates both methods (and btw another glitch when matching strings without knowing the pitfalls).

[mriosv]

Other way, a solution using SUMPRODUCT() or SUM() in array form, avoiding the regex and order.

[dithyramble]

mriosv's solution works for me.