Home Forums HCL Reviews Tutorials Articles Register Search Today's Posts Mark Forums Read
 Linux - Software This forum is for Software issues. Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

 12-08-2007, 08:21 AM #1 win32sux Guru   Registered: Jul 2003 Location: Los Angeles Distribution: Ubuntu Posts: 9,870 Rep: Calc Spreadsheet VLOOKUP Question 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. Last edited by win32sux; 12-08-2007 at 08:33 AM.
12-08-2007, 10:52 AM   #2
allend
Senior Member

Registered: Oct 2003
Location: Melbourne
Distribution: Slackware-current
Posts: 3,520

Rep:
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

Last edited by allend; 12-08-2007 at 11:05 AM. Reason: Adding further detail

 12-08-2007, 07:03 PM #3 win32sux Guru   Registered: Jul 2003 Location: Los Angeles Distribution: Ubuntu Posts: 9,870 Original Poster Rep: 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? Last edited by win32sux; 12-08-2007 at 08:08 PM.
 12-08-2007, 08:47 PM #4 allend Senior Member   Registered: Oct 2003 Location: Melbourne Distribution: Slackware-current Posts: 3,520 Rep: Hi win32sux. Yes, removing the checkmark that you describe does make the MATCH function work as expected. Thanks for the tip!
 12-08-2007, 09:29 PM #5 Tinkster Moderator   Registered: Apr 2002 Location: in a fallen world Distribution: slackware by choice, others too :} ... android. Posts: 23,005 Blog Entries: 11 Rep: 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