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" function, true or false.... but it's not enou

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


Joined: 12 Dec 2004
Posts: 2

PostPosted: Sun Dec 12, 2004 4:32 am    Post subject: "If" function, true or false.... but it's not enou Reply with quote

'ello, new user, in case you can't tell. I've looked around the past posts to see if what I need has already been adressed, but without any luck. So, without further ado...

I need a multi-pronged "if" function. Let me elaborate;

The "if" function, as far as I can tell, has only two possible results - one if the statement is true, and another if the statement is false.

What I'm trying to do, directly or in a roundabout way is something along the lines of this;

If{B1=0} use equation #0 and display the result
If{B1=1} use equation #1 and display the result
If{B1=2} use equation #3 and display the result
If{B1=4} use equation #0 and display the result
If none of the above is true, the result is "Something is wrong"

And so far, I haven't managed to figure out a way to do it with the functions at my disposal.

Thanks in advance,

Quinch
Back to top
View user's profile Send private message AIM Address
Colorado6202
General User
General User


Joined: 27 Sep 2004
Posts: 22

PostPosted: Sun Dec 12, 2004 5:43 am    Post subject: Reply with quote

OK, here's how I did it:

I put equation 0 in cell E1
I put equation 1 in cell E2
I put equation 3 in cell E3
I also named these cells Equation0, Equation1, & Equation3, using INSERT>NAMES. This is optional, as you could also use the cell addresses in the formula below. Also, be sure to use an equal sign before each equation when typing them into the cells.

Then in the cell where I want the results returned, I put the following nested IF formula:

=IF(B1=0;Equation0;IF(B1=1;Equation1;IF(B1=2;Equation3;IF(B1=4;Equation0;"Something is wrong"))))

This formula says If B1=0, then use equation 0; unless if B1=1 then use equation 1, unless B1=2 then use equation3; unless B1=4 then use equation 0, or if B1 is anything else, return the string in the quotes (which happens to be "Something is wrong"). Basically, I am using another IF formula for the second argument (the false) for the previous IF formula.

Remember to use a close parentheses for every open one.

Hope this helps!
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Dec 12, 2004 7:09 am    Post subject: Reply with quote

Colorado6202 wrote:
OK, here's how I did it:

I put equation 0 in cell E1
I put equation 1 in cell E2
I put equation 3 in cell E3
I also named these cells Equation0, Equation1, & Equation3, using INSERT>NAMES. This is optional, as you could also use the cell addresses in the formula below. Also, be sure to use an equal sign before each equation when typing them into the cells.

Then in the cell where I want the results returned, I put the following nested IF formula:

=IF(B1=0;Equation0;IF(B1=1;Equation1;IF(B1=2;Equation3;IF(B1=4;Equation0;"Something is wrong"))))

This formula says If B1=0, then use equation 0; unless if B1=1 then use equation 1, unless B1=2 then use equation3; unless B1=4 then use equation 0, or if B1 is anything else, return the string in the quotes (which happens to be "Something is wrong"). Basically, I am using another IF formula for the second argument (the false) for the previous IF formula.

Remember to use a close parentheses for every open one.

Hope this helps!


My first approach too. However, if there are a lot of formulas, there is also the option of putting numbers and formulas into two columns and using Vlookup. Hide those columns.
The calculations are actually done in the formula column, and the value sent to the vlookup request.

David.
Back to top
View user's profile Send private message
Colorado6202
General User
General User


Joined: 27 Sep 2004
Posts: 22

PostPosted: Sun Dec 12, 2004 7:44 am    Post subject: Reply with quote

David wrote:

My first approach too. However, if there are a lot of formulas, there is also the option of putting numbers and formulas into two columns and using Vlookup. Hide those columns.
The calculations are actually done in the formula column, and the value sent to the vlookup request.

David.


David, I thought about that, but doesn't VLOOKUP fail in this instance? You have two values that return the result of Equation 0. Also, would VLOOKUP find the closest numeric value and use the formula rather than returning the error message?

I imagine their is a better way to do it that is more scaleable than what I posted. I would like to see how you would set this up. You are absolutely right...if there are lots of formulae you hit the limit on nested IF functions, plus your solution would definitely be more elegant and easier to follow. Can you post more detail?

Thanks!
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Dec 12, 2004 11:50 am    Post subject: Reply with quote

Colorado6202 wrote:
David wrote:

My first approach too. However, if there are a lot of formulas, there is also the option of putting numbers and formulas into two columns and using Vlookup. Hide those columns.
The calculations are actually done in the formula column, and the value sent to the vlookup request.

David.


David, I thought about that, but doesn't VLOOKUP fail in this instance?
Thanks!


Sigh.. Yes. I guess the brain goes second. I can't remember what goes first.

David.
Back to top
View user's profile Send private message
Colorado6202
General User
General User


Joined: 27 Sep 2004
Posts: 22

PostPosted: Sun Dec 12, 2004 12:24 pm    Post subject: Reply with quote

David wrote:


Sigh.. Yes. I guess the brain goes second. I can't remember what goes first.

David.


I could tell you if I could remember! Laughing

Anyway, I still think you are onto the right idea. I am sure there is a better way than deep nesting IFs, but I don't have enough brain cells awake at the moment to figure it out. I am sure someone in our great OOo community will be able to tell us. I suspect maybe using the MATCH function could be the answer.
Back to top
View user's profile Send private message
r_vinoya
Super User
Super User


Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines

PostPosted: Sun Dec 12, 2004 7:09 pm    Post subject: Reply with quote

Colorado6202 wrote:
David wrote:

My first approach too. However, if there are a lot of formulas, there is also the option of putting numbers and formulas into two columns and using Vlookup. Hide those columns.
The calculations are actually done in the formula column, and the value sent to the vlookup request.

David.


David, I thought about that, but doesn't VLOOKUP fail in this instance? You have two values that return the result of Equation 0. Also, would VLOOKUP find the closest numeric value and use the formula rather than returning the error message?

I imagine their is a better way to do it that is more scaleable than what I posted. I would like to see how you would set this up. You are absolutely right...if there are lots of formulae you hit the limit on nested IF functions, plus your solution would definitely be more elegant and easier to follow. Can you post more detail?

Thanks!


I think VLOOKUP would work:
1) You must set "sort order" to false (default is true)
2) Add IF(ISNA(VLOOKUP(***)) conditon to display "Something is Wrong"
3) Add IF(B1="";""; *****) so that vlookup would not "FALSE TRIGGER" as 0.

So the equation would look something like:
Code:
=IF(B1="";"";IF(ISNA(VLOOKUP(B1; X1:Y4; 2; 0));"Something is Wrong";VLOOKUP(B1; X1:Y4; 2; 0)))

Where X1:Y4 contains the formula references
_________________
# : - )
Back to top
View user's profile Send private message
Quinch
Newbie
Newbie


Joined: 12 Dec 2004
Posts: 2

PostPosted: Tue Dec 14, 2004 1:24 am    Post subject: Reply with quote

Cool, I figured it out now. Thanks for the help.

Regards,

Quinch
Back to top
View user's profile Send private message AIM Address
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