Apache OpenOffice Community Forum

Hey,

what's wrong with my fornula?
I attached screenshot to the post.

Both columns should be identical... shouldn't they?
In case the picture isn't too clear, the right row contains the formula and I typed in values in the left one.

I have Enable regular expressions in formula checked and Search criteria = and <> must apply to whole cell unchecked. Anyway I tried all combinations of check/uncheck, nothing changed. I'm guessing it has to do with the + and - symbols, but I can't figure out how to fix it... Can you guys help me out?

Thanks a lot

The lookup vector has to be sorted in ascending order which would be A, A-, A+, B, B-, B+ ....

Ah! I see. That's probably what they mean in the doc by "searchtable must be sorted, with numbers in ascending order appearing before text values in alphabetic order. " -_-

And I also had to change my resulttable order, for it to fit the new order of the lookup vector.

Well, that was simple. And I'm a dummy, will learn to read next time. Thanks for the quick response!!

edit:
Here's the formula that worked for me: A+ = 4.33, A = 4.00, A = 3.67, ... D = 1.00, E = 0.00.

Thanks for the words of advice Villeroy

Even with a sorted search vector you get wrong positive results for arbitrary text value. LOOKUP does not do what most people think it would do. It does not look up one distinct item from a list.
Another issue is Tools>Options>Calc>Calculation: "Enable regular expressions in formulas" which should be off, particularly when special characters ^$+.*?<>{}[]() are involved. Option "= and <> must apply to whole cells" should be on, particularly when you need to distinguish "Axx" from "Ax" and "A".