OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

if/or

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
oblivious
Newbie
Newbie


Joined: 19 Apr 2009
Posts: 1

PostPosted: Sun Apr 19, 2009 11:32 pm    Post subject: if/or Reply with quote

I'm trying to figure out how to set up a spreadsheet so that the first cell in a row will assign a value to several of the next cells. I'd like something along the lines of

<if A1 is 1 or 5 or 223 or 501>then B1 returns "true"
that much I've figured out, the trick is I then want

<if A1 is 2 or 6 or 18 or 1000> then B1 returns "penguins"
<if A1 is 17 or 95 or 103 or 1200>t hen B1 returns "elephants"

<if A1 is greater than 400> C1 returns "Big"
<if A1 is less than 200> C1 returns "Small"
<if A1 is between 200 and 400> C1 returns "Between"

I can't seem to get the syntax right to use the ifs and ors in conjunction with each other.
Does anyone know how to do this, or if it is even possible?

Also if this could be set up in a format that can be copied to the next row without change (So it applies from A1 to B1/C1 and copies straight to B2/C2 to read from A2 etc) it would be a great help. I know I could do that in excel so I assume it's possible here(I'm brand new to ooo).

My thanks in advance.
Back to top
View user's profile Send private message
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Mon Apr 20, 2009 1:10 am    Post subject: Reply with quote

Although you can nest if & or, it will get confusing to balance the brackets etc.

A clearer approach is to use a look-up-table like this:
2 penguins
6 penguins
18 penguins
1000 penguins
17 elephants
95 elephants
103 elephants
1200 elephants

0 Small
200 Between
400 Big

In cells G1:H12

You then need the formula in B1:

Code:

=VLOOKUP(A1;G1:H8;2;0)


and in C1

Code:
=VLOOKUP(A1;G10:H12;2)


Note the C1 formula automatically handles greater/less than.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group