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

How do I create a simple nested if statement

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





PostPosted: Sat Apr 17, 2004 1:04 pm    Post subject: How do I create a simple nested if statement Reply with quote

Hi,

SHORT VERSION:

Thanks in advance.

First off, I've read some or much of the first 5 pages of the forum for Calc, where I used the key search words "nested function", where 226 matches returned. "nested if function" returned 227 matches. Also, I think that I am not interested in Vlookup, Hlookup, or similar. I want a simple, non-array, non-developer/programmer way of just using a fomula such as:

=IF (D1=single; $1; =IF (D1=double; $2;=IF (D1=triple; $2.25;=IF (D1=quad; $2;0))))

or,

IF(size='SINGLE',1, IF(size='double',2, IF(size='triplemambo',2.75,IF(size='quadmambo',4.25, 0))))


----
LONG VERSION LONG VERSION LONG VERSION LONG VERSION:

I have a column, D, which can receive multiple values: single, double, triple, quad.

I want the column K to automatically optionally enter/show a number value or text based on the value in column D.

For instance, in the now 4-row table:

Column D K
Row ConeSize Price

1 single $1.00
2 double $2.00
3 triple $2.25
4 quad $3.00


I want to be able to add more rows which will repeat single, double, triple, or quad in the growing table. Currently,

there is available the "if" statement, but it only seems to permit only one type of result. For example,

=IF (D1=Single; $1; 0).

BUT, I want to have a workng statment such as:


=IF (D1=single; $1; =IF (D1=double; $2;=IF (D1=triple; $2.25;=IF (D1=quad; $2;0))))


This does not seem to be possible in OO.o. I can do it in Lotus Approach (database) and Lotus 1-2-3

----

In Lotus 1-2-3, the if statement has this help note:

Notes

@IF is useful when combined with @ERR and @NA to document errors or missing data in formulas. It is also useful in

preventing ERR, NA, and calculation errors in situations where data may be missing or inaccurate, for example, to

prevent division by zero.

You can nest @IF functions within one another to create a complex condition. For example,
@IF(TOT>10000;TOT*0.15;@IF(TOT>5000;TOT*0.10;TOT*0.02))
nests two @IF functions to determine a commission rate based on three levels of sales: total sales greater than

$10,000, total sales greater than $5,000, and total sales less than or equal to $5,000.

Examples

@IF(BALANCE>=0;BALANCE;"Overdrawn") returns the value in the cell named BALANCE when the value in BALANCE is 0 or

positive; or returns the label Overdrawn when the value in BALANCE is negative.

------

In Lotus Approach, the if statement has this help note:


IF(size='SINGLE',1, IF(size='double',2,0))

To grow or nest this if function, I simply, as above, enter a corresponding opening "(" and closing ")" for each

condition being checked. For example, I decide I want to permit in the same cells some additional values:


triplemambo

quadmambo


I just modify the statement to be:



IF(size='SINGLE',1, IF(size='double',2, IF(size='triplemambo',2.75,IF(size='quadmambo',4.25, 0))))


=======


I should be able to nest 7 or more statements, as long as each opening "(" is complemented by a closing ")"

The ability to do this in OO.o would save considerable time, reduce errors, and create much flexibility.


I have visited:

http://www.ooomacros.org/

So, how do I do this in OO.o/SO? Did I overlook something the average user could find?
Back to top
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Sat Apr 17, 2004 2:36 pm    Post subject: Reply with quote

example of nested IF
=IF(D1="single";1;IF(D1="double";2;IF(D1="triple";3;IF(D1="quad";4;"None of the above"))))

note: use double quotes (") for string matching and semicolons( ; ) for separating arguments in OOo CALC functions

Also string matching is case sensitive so you may wish to use the LOWER function as in
=IF(LOWER(D1)="single";1;IF ...
Back to top
View user's profile Send private message
Guest






PostPosted: Sat Apr 17, 2004 3:25 pm    Post subject: Reply with quote

dfrench wrote:
example of nested IF
=IF(D1="single";1;IF(D1="double";2;IF(D1="triple";3;IF(D1="quad";4;"None of the above"))))

note: use double quotes (") for string matching and semicolons( ; ) for separating arguments in OOo CALC functions

Also string matching is case sensitive so you may wish to use the LOWER function as in
=IF(LOWER(D1)="single";1;IF ...


Or leave the last as "" [no 'space'] for an empty cell.

Another approach:
=(D1="single") + (D1="double")*2 + (D1="triple")*3 + (D1="quad")*4

This is based on TRUE=1, FALSE=0, so only the true statement has a value of 1 * the associated amount.

If wanting a blank cell instead of a '0' which would occur if D1 was empty, typing could be saved by putting [say your formula is in E1] into F1, =IF(E1=0;"";E1). It's often a good idea not to cram everything into one cell, and there are columns to spare.

David.
Back to top
Doug J
General User
General User


Joined: 13 Apr 2004
Posts: 10

PostPosted: Sat Apr 17, 2004 4:37 pm    Post subject: Re: How do I create a simple nested if statement Reply with quote

davidapproach wrote:
Hi,


. Also, I think that I am not interested in Vlookup, Hlookup, or similar.



Why aren't you interested in vlookup?

If you have more than about 3 levels of nesting a lookup table is easier to use, easier to understand, and easier to update. Why use multiple ifs?
Back to top
View user's profile Send private message
davidapproach
Guest





PostPosted: Sun Apr 18, 2004 10:57 am    Post subject: vlookup Reply with quote

Maybe, I suppose, it's "resitance to change", tho resistance can be "futile".

Well, if there are no plans to support or enable multiple if statments, then (and I haven't looked at vlookup/hlookup for more than enough seconds to feel some anxiety...) will vlookup or hlookup or "x"lookup permit me to cut and paste my successful lookups to a text document? It is regimented in me that I copy my working and failing examples of if statements so that I can be sure not to waste effort or energy starting from scratch.

Also, is there possibly *anyone* in this forum who will admit to having used Lotus Approach? I find it saddening that IBM won't bring SmartSuite into the Linux age, and I find it astonishing that the award-winning database front-end has not been rigorously imitated in the Open Source community. I am not proposing to outright infringe on patents or such, but so much in the computer world ought to be reduced to "inevitable/multiple discovery" so that infringement is less of a threat.

I suggest that because to me the patent system is nothing but legal piracy on the creation seas. It's a form of anti-poor-developer imperialism that means slow, plodding, tinkering, non-developer-but-with-good-ideas cannot just on my own time introduce them without fear of infringement suits. I know my macros and scripts won't be copy-n-pastes of others' more advanced works, but if I can do it in Approach I feel it should be replicable with today's tools.

So, if anyone here has admittedly used and actually liked aspects of Approach, what would it take to get the features into Star Office and Open Office...

Say, I just realized, THIS could be why microsoft and Sun settled out of court.. For the past year or so, I've been lamenting and hammering away in various forums asking IBM and Sun to get together and merge the BEST of Lotus SmartSuite, Sun's Star Office and OpenOffice.org's OpenOffice. I guess FINALLY someone in microsoft sees so much of a threat in my ideas that they schemed to pay off Sun and call it a truce to save money. I now, at this moment suspect that microsoft is shaking in it's boots.

Does anyone here think it is an idea that Groklaw should be examining? Maybe it could further cast a pall over ms attempts at code hegemony.

I digress. I would like to see a separate, non-calc interface to be the database front end and management system. I think calc should remain used as a spreadsheet, statistics or similar too, but personally I think spreadsheets, laking referential integrity, lacking many input constraints/filters, and lacking the visual file/container metaphor for a database and its tables makes it frustrating for me to use spreadsheet interfaces rather than table-like, Approach or Filemaker interfaces.

Finally, can anyone point to a good reference about the vlookup and hlookup history? If they come from excel, and if they are something that not even ms is suing over, then surely Lotus Approach can be imitated by the Open Source community. Afterall, TheKompany tried it with Rekall, but for me it is not far nor close enough. Would KDevelop, QT3 or others be able to mimic it and make a good Linux-based tool?

Thanks for reading thru all this.

Kind Regards,

David Syes
Back to top
Doug J
General User
General User


Joined: 13 Apr 2004
Posts: 10

PostPosted: Sun Apr 18, 2004 3:34 pm    Post subject: Re: vlookup Reply with quote

Hi David

You obviously feel strongly about this Smile

First of all, multiple if statements do work. Just copy and paste the example from dfrench (you might need to switch off auto-correct, or capitalise the first letter of the words in the if statements)

Secondly, using vlookup was just a suggestion. That's the way I do it, and it suits me, but it's certainly not the only way to do it.

davidapproach wrote:

Well, if there are no plans to support or enable multiple if statments, then (and I haven't looked at vlookup/hlookup for more than enough seconds to feel some anxiety...) will vlookup or hlookup or "x"lookup permit me to cut and paste my successful lookups to a text document? It is regimented in me that I copy my working and failing examples of if statements so that I can be sure not to waste effort or energy starting from scratch.


If you do decide to try out lookups, yes you can paste to a text document (I'd leave the documentation in a spreadsheet myself, but that's me)

Quote:
Also, is there possibly *anyone* in this forum who will admit to having used Lotus Approach? I find it saddening that IBM won't bring SmartSuite into the Linux age, and I find it astonishing that the award-winning database front-end has not been rigorously imitated in the Open Source community. I am not proposing to outright infringe on patents or such, but so much in the computer world ought to be reduced to "inevitable/multiple discovery" so that infringement is less of a threat.


I've used Approach, it's OK, but I'm just not a database sort of person, sorry



Quote:
Say, I just realized, THIS could be why microsoft and Sun settled out of court.. For the past year or so, I've been lamenting and hammering away in various forums asking IBM and Sun to get together and merge the BEST of Lotus SmartSuite, Sun's Star Office and OpenOffice.org's OpenOffice. I guess FINALLY someone in microsoft sees so much of a threat in my ideas that they schemed to pay off Sun and call it a truce to save money. I now, at this moment suspect that microsoft is shaking in it's boots.

Does anyone here think it is an idea that Groklaw should be examining? Maybe it could further cast a pall over ms attempts at code hegemony.

I digress. I would like to see a separate, non-calc interface to be the database front end and management system. I think calc should remain used as a spreadsheet, statistics or similar too, but personally I think spreadsheets, laking referential integrity, lacking many input constraints/filters, and lacking the visual file/container metaphor for a database and its tables makes it frustrating for me to use spreadsheet interfaces rather than table-like, Approach or Filemaker interfaces.


I very much doubt that MS are at all worried about the threat from any part of Smartsuite. I've never been able to understand why everyone uses Excel (which drives me up the wall) when 123 is around, albeit receiving minimal development effort. I'd love to see 123 made open source, and the best bits included in OOo, but I doubt if it's going to happen.

Quote:
Finally, can anyone point to a good reference about the vlookup and hlookup history? If they come from excel, and if they are something that not even ms is suing over, then surely Lotus Approach can be imitated by the Open Source community. Afterall, TheKompany tried it with Rekall, but for me it is not far nor close enough. Would KDevelop, QT3 or others be able to mimic it and make a good Linux-based tool?


Vlookup and hlookup have certainly been around since the first version of Lotus 123 (1983??). They were probably in Visicalc, but I don't know for sure. If Lotus thought they could sue for infringement of the lookup concept they would have done it, they wern't slow to sue in the eighties; maybe if they'd spent more time developing and less time suing then Smartsuite would still be a player.

Hope that helps.

Do try the multiple If statements again, they should do what you want.

Doug[/quote]
Back to top
View user's profile Send private message
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