Lookup table for grade calculation
-
- Posts: 302
- Joined: Tue Mar 04, 2008 9:43 am
- Location: Vancouver, BC
Lookup table for grade calculation
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."
- Attachments
-
- grade.look-up.sample.ods
- (8.26 KiB) Downloaded 1119 times
LibreOffice 5.2.5.1 on MacBook OS X 10.12.3, "Sierra"
Re: lookup table for grade calculation
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.
=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.
Re: lookup table for grade calculation
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).
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).
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: Lookup table for grade calculation
JohnV wrote:I changed your formula... and then did Tools > Options > Calc > Calculate and unchecked Regular Expressions.
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.keme wrote:Another approach..., and your formula will work, as soon as the use of regex in formulas is disabled (see JohnV's post).
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.
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.
Last edited by acknak on Thu Oct 22, 2009 6:05 pm, edited 4 times in total.
Reason: Corrections
Reason: Corrections
AOO4/LO5 • Linux • Fedora 23
Re: Lookup table for grade calculation
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)
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)
Re: Lookup table for grade calculation
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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Lookup table for grade calculation
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.
Maybe I should just delete that post
Ack! Good catch. I was even thinking "2" when I typed it.Yours is is an excellent solution but contains a typo, i.e., 1 instead of 2:...
Maybe I should just delete that post
AOO4/LO5 • Linux • Fedora 23
-
- Posts: 302
- Joined: Tue Mar 04, 2008 9:43 am
- Location: Vancouver, BC
Re: Lookup table for grade calculation
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.
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.
LibreOffice 5.2.5.1 on MacBook OS X 10.12.3, "Sierra"
Re: Lookup table for grade calculation
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.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).
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).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Lookup table for grade calculation
Other way, a solution using SUMPRODUCT() or SUM() in array form, avoiding the regex and order.
- Attachments
-
- grade.look-up.sample.ods
- (11.24 KiB) Downloaded 636 times
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
-
- Posts: 1
- Joined: Mon Nov 07, 2011 6:56 am
Re: Lookup table for grade calculation
mriosv's solution works for me.
OpenOffice 3.1 on Ubuntu 9.10.