LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
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

Reply
 
Search this Thread
Old 12-08-2007, 07:21 AM   #1
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Question 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 07:33 AM.
 
Old 12-08-2007, 09:52 AM   #2
allend
Senior Member
 
Registered: Oct 2003
Location: Melbourne
Distribution: Slackware-current
Posts: 3,379

Rep: Reputation: 824Reputation: 824Reputation: 824Reputation: 824Reputation: 824Reputation: 824Reputation: 824
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 10:05 AM. Reason: Adding further detail
 
Old 12-08-2007, 06:03 PM   #3
win32sux
Guru
 
Registered: Jul 2003
Location: Los Angeles
Distribution: Ubuntu
Posts: 9,870

Original Poster
Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
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 07:08 PM.
 
Old 12-08-2007, 07:47 PM   #4
allend
Senior Member
 
Registered: Oct 2003
Location: Melbourne
Distribution: Slackware-current
Posts: 3,379

Rep: Reputation: 824Reputation: 824Reputation: 824Reputation: 824Reputation: 824Reputation: 824Reputation: 824
Hi win32sux. Yes, removing the checkmark that you describe does make the MATCH function work as expected. Thanks for the tip!
 
Old 12-08-2007, 08:29 PM   #5
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,966
Blog Entries: 11

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


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
openoffice.org calc question hexter Linux - Desktop 5 09-02-2006 10:46 PM
OOo spreadsheet format question. tardigrade Linux - Software 3 01-20-2005 01:47 PM
openoffice calc question buffed317 Linux - General 1 09-11-2004 03:27 PM
OpenOffice Calc formula question Bigun Linux - Software 2 08-16-2004 03:07 PM
openoffice calc question... marsques Linux - Software 2 07-10-2004 04:30 PM


All times are GMT -5. The time now is 05:43 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration