Apache OpenOffice Community Forum

Hi, I'm trying to create an IF function that outputs point values in the following way:

• If a cell value (let's say cell $X$1) is less than or equal to (let's say) -0.1, the point value I want it to return is 100
  If the cell value is between -0.1 and 0.1, I want it to return 0.
  If the cell value is greater than or equal to 0.1, I want it to return -100
  If none of these conditions in met (a logical impossibility) I want it to return an absurd number to call my attention to the problem, let's say 100000000

So I made a concatenated series of IF formulas, which works well enough except in the middle range, where the value is between -0.1 and 0.1. In those cases it does not recognize, for instance, a value of -0.06 as falling within the range I specified... I must be doing something wrong, but I can't see it. The code I used for this range is
When cell $X$1 is populated with the value -0.06, this formula returns my absurd value of 100000000.

Is it not allowed to have a tested value be between two different values? Is there a better way to do this? The simple value tests (less than or equal to -0.1 and g. than or = to 0.1) work just fine, it's only this one with the cell value sandwiched between two other values that it breaks down.

Thanks for your help or ideas.

Dave

You must be to use the AND/OR logical function for more than one condition test.

Tibor's fix using AND is the correct one. What is happening with
is that -0.1<$X$1 is evaluated first, giving 1 (TRUE), then 1 < .1 is evaluated, which is FALSE, of course.

Is there a better way to do this?

I prefer to use a lookup table for jobs like this:

F G
For values >= ... Return
-1e308 100
-.1001 0
0.1 -100

=LOOKUP(A1; F2:F4; G2:G4)

Thanks, Zizi, Charlie and acknack. This makes a lot of sense now, at least I understand how it works, very clear. It's bedtime here, I'll give it a go tomorrow both ways and see. I've never used a lookup table before and I don't quite understand how to do it but I will have a look at the help and such and see what I come up with. I think Zizi's solution might work better for me as I have about 30 columns all with different point calculations to spit out times a million bijillion rows, so making the lookup table might make my head hurt...

Thanks,

Dave

Here's a simple example...

Yes, it is a bit of trouble to set up the tables, and you have to remember to always keep them sorted in ascending order or the lookup will return nonsense. But it's far less trouble, and far easier to be sure that the logic is right, than using discrete formulas. At least it is for me.

Acknack, thanks so much for the example spreadsheet... very helpful for learning the lookup function, and I also learned the FORMULA () function, which I didn't know existed -- manna from heaven!

The lookup function is JUST what I need for many reasons...

• I can change the point distribution easily at any time simply by updating the table (which I will need to do occasionally)
  All the assumptions are clearly listed in front of me in the table
  No need for endless reworking of the formula, copy and fill down

I do need a lot of lookup tables for what I'm doing, so I'm just hiving them off to a separate sheet, where I can look at them all together.

Thank you again for this suggestion, this user community is the most helpful I've seen anywhere -- long live Open Office!

Dave

Yes!

Sounds like you're on the trolley

Yes indeed!