Latest LQ Deal: Linux Power User Bundle
 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 LQ 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: 4,693

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 LQ 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: 4,693 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,067 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

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post hexter Linux - Desktop 5 09-02-2006 11:46 PM tardigrade Linux - Software 3 01-20-2005 02:47 PM buffed317 Linux - General 1 09-11-2004 04:27 PM Bigun Linux - Software 2 08-16-2004 04:07 PM marsques Linux - Software 2 07-10-2004 05:30 PM

LinuxQuestions.org

All times are GMT -5. The time now is 10:29 PM.

 Contact Us - Advertising Info - Rules - LQ Merchandise - Donations - Contributing Member - LQ Sitemap -