Calc Spreadsheet VLOOKUP Question

win32sux · 12-08-2007, 07:21 AM

Hi. I'm using OpenOffice.org 2.3 on Ubuntu 7.10. I've got a question about VLOOKUP in Calc. I'm trying to get it to work with letter grades (I'm calculating my GPA), but I've run into trouble. Let's say I have two tables:

Table 1:

Code:

EBUS311      3         A+        !!!

Table 2:

Code:

A+     4.33
A      4.00
A-     3.67
B+     3.33
B      3.00
B-     2.67

In the first table, the columns are: Course, Credits, Grade, and Quality Points. The "!!!" in the Quality Points cell represents what I'm trying to create the VLOOKUP for. In the second table I basically have the numerical equivalents for the letter grades. The idea is I input the letter grades in the first table, and calculations are done (for the quality points) using the number equivalent in the second table.

Basically, I want to VLOOKUP A3 (from the first table) in the second table, and get the value of the second cell, so that I can multiply it by A2 (first table) to get the quality points (quality points is equal to grade times credits). I'm using something like this in A4 (!!!):

Code:

=VLOOKUP(A3;Table2.A1:B6;2;FALSE)*A2

The problem seems to be with the plus sign (+) or something (I think). The VLOOKUP returns the value of "A", which is 4.00, instead of the value of "A+" (4.33). If I use an "A-" it works fine, returning a 3.67.

What is going on here? It's been a while since I've used a spreadsheet so I'm pretty rusty. I know I could get this to work by using percentages, but I'd really like to stick to letter grades. Any help would be greatly appreciated.

allend · 12-08-2007, 09:52 AM

I think you have found a bug in OO 2.3 calc

Quote:

A+ 4.33 2 "=MATCH(A8;$A$1:$A$6;0)
A 4 2 "=MATCH(A9;$A$1:$A$6;0)
A- 3.67 3 "=MATCH(A10;$A$1:$A$6;0)
B+ 3.33 5 "=MATCH(A11;$A$1:$A$6;0)
B 3 5 "=MATCH(A12;$A$1:$A$6;0)
B- 2.67 6 "=MATCH(A13;$A$1:$A$6;0)

A+
A
A-
B+
B
B-

I was looking at the MATCH function as a possibility, but found this.
Note the result of the MATCH function using the formula shown afterward.
The + sign causes a glitch.

And it does work for text strings.

Quote:

AA 4.33 1 "=MATCH(A8;$A$1:$A$6;0)
AB 4 2 "=MATCH(A9;$A$1:$A$6;0)
AC 3.67 3 "=MATCH(A10;$A$1:$A$6;0)
BA 3.33 4 "=MATCH(A11;$A$1:$A$6;0)
BB 3 5 "=MATCH(A12;$A$1:$A$6;0)
BC 2.67 6 "=MATCH(A13;$A$1:$A$6;0)

AA
AB
AC
BA
BB
BC

win32sux · 12-08-2007, 06:03 PM

Hi allend. Thanks man, I really appreciate you taking the time to test this. I think I might have found what is triggering this behavior. I went to Tools > Options > OpenOffice.org Calc > Calculate and I removed the checkmark from the "Enable regular expressions in formulas" box. Doing so makes the VLOOKUP work as expected. Can you (or anyone else) confirm this?

allend · 12-08-2007, 07:47 PM

Hi win32sux. Yes, removing the checkmark that you describe does make the MATCH function work as expected. Thanks for the tip!

Tinkster · 12-08-2007, 08:29 PM

I feel that this post is better of in Linux-Software since it's not about
OO under windows, and have moved it accordingly. :}

Cheers,
Tink