[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
davidapproach
Guest

dfrench
Moderator

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

 Posted: Sat Apr 17, 2004 2:36 pm    Post subject: 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 ...
Guest

Posted: Sat Apr 17, 2004 3:25 pm    Post subject:

 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.
Doug J
General User

Joined: 13 Apr 2004
Posts: 10

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

 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?
davidapproach
Guest

 Posted: Sun Apr 18, 2004 10:57 am    Post subject: vlookup 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
Doug J
General User

Joined: 13 Apr 2004
Posts: 10

Posted: Sun Apr 18, 2004 3:34 pm    Post subject: Re: vlookup

Hi David

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]
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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