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