View previous topic :: View next topic 
Author 
Message 
Quinch Newbie
Joined: 12 Dec 2004 Posts: 2

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


'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 multipronged "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 


Colorado6202 General User
Joined: 27 Sep 2004 Posts: 22

Posted: Sun Dec 12, 2004 5:43 am Post subject: 


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 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Sun Dec 12, 2004 7:09 am Post subject: 


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 


Colorado6202 General User
Joined: 27 Sep 2004 Posts: 22

Posted: Sun Dec 12, 2004 7:44 am Post subject: 


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 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Sun Dec 12, 2004 11:50 am Post subject: 


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 


Colorado6202 General User
Joined: 27 Sep 2004 Posts: 22

Posted: Sun Dec 12, 2004 12:24 pm Post subject: 


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!
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 


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

Posted: Sun Dec 12, 2004 7:09 pm Post subject: 


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 


Quinch Newbie
Joined: 12 Dec 2004 Posts: 2

Posted: Tue Dec 14, 2004 1:24 am Post subject: 


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

Back to top 




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
