| View previous topic :: View next topic |
| Author |
Message |
beerhost
Red Pill
Joined: Apr 27, 2009
Posts: 6
|
 Posted: Mon Apr 27, 2009 4:24 pm Post subject: Formula help |
|
This formula works in Google Docs, but not in NeoOffice:
=(CHOOSE(MATCH(E4,{"Virginia";"Pennsylvania";"Maryland";"D.C.";"Delaware";"West Virginia";"New Jersey"},0),"Va.","Pa.","Md.","D.C.","Del.","W.Va.","N.J."))
Can someone tell me what I am doing wrong? |
|
| Back to top |
|
 |
pluby
The Architect
Joined: Jun 16, 2003
Posts: 11949
|
| Posted: Mon Apr 27, 2009 6:17 pm Post subject: |
|
I got your formula to work by replacing all "," characters with ";" characters so that the formula looks like this:
| Code: | | =(CHOOSE(MATCH(E4;{"Virginia";"Pennsylvania";"Maryland";"D.C.";"Delaware";"West Virginia";"New Jersey"};0);"Va.";"Pa.";"Md.";"D.C.";"Del.";"W.Va.";"N.J.")) |
In general, NeoOffice's underlying OpenOffice.org code does not recognize a "," as a separator in formulas whereas some versions of Microsoft Office do.
Does replacing the "," characters with ";" characters work for you?
Patrick |
|
| Back to top |
|
|
beerhost
Red Pill
Joined: Apr 27, 2009
Posts: 6
|
| Posted: Wed Apr 29, 2009 4:14 am Post subject: |
|
I am still getting #NAME? in the field, even with the ; instead of the ,
=CHOOSE(MATCH(E3{"Virginia";"Pennsylvania";"Maryland";"D.C.";"Delaware";"West Virginia";"New Jersey"};0);"Va.";"Pa.";"Md.";"D.C.";"Del.";"W.Va.";"N.J.")
Any ideas? I am trying to substitue AP Style abbreviation for a state name. Any suggestions would be helpful! |
|
| Back to top |
|
|
Lorinda
Captain Mifune
Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA
|
| Posted: Wed Apr 29, 2009 5:38 am Post subject: |
|
Comparing your second formula to Patrick's, it looks like you are missing a parenthesis at the end of the formula.
Lorinda |
|
| Back to top |
|
|
beerhost
Red Pill
Joined: Apr 27, 2009
Posts: 6
|
| Posted: Wed Apr 29, 2009 6:01 am Post subject: |
|
I actually got a neooffice warning asking me to correct the formula...it removed that last ). But now I am doing it, and it still does nothing but give me #NAME? I want it to give me Va. for Virginia, Pa. for Pennsylvania, etc., when E4 is equal to that.
=(CHOOSE(MATCH(E4;{"Virginia";"Pennsylvania";"Maryland";"D.C.";"Delaware";"West Virginia";"New Jersey"};0);"Va.";"Pa.";"Md.";"D.C.";"Del.";"W.Va.";"N.J."))
The function wizard has issues with it. Am I going about this the wrong way? Thanks for all of your help. |
|
| Back to top |
|
|
beerhost
Red Pill
Joined: Apr 27, 2009
Posts: 6
|
| Posted: Wed Apr 29, 2009 6:21 am Post subject: |
|
| BTW, I am using 2.2 - could this be a version issue or a format issue? |
|
| Back to top |
|
|
James3359
The Merovingian
Joined: Jul 05, 2005
Posts: 685
Location: North West England
|
| Posted: Wed Apr 29, 2009 7:40 am Post subject: |
|
I pasted your formula into a NO 3 Calc cell, and it worked fine. In NO 2.2.5 Patch 8 it failed. Some searching in OOo sources located this OOo wiki page and the following information | Quote: | | In releases before 3.0, MATCH cannot accept an array for the second parameter, as required by the forthcoming international standard ODFF (issue 8947). For example MATCH(2;{1;2;3};0) returns Err:504. This form is supported by OpenOffice.org Calc versions 3.0 and later, giving the correct result: |
This is bad news, I'm afraid, for 2.2.5 users as the NeoOffice project resources are such that it is most likely that this change cannot be implemented for NO 2.2.5 users as that would mean altering the underlying OOo code. Someone will probably confirm this in due course. |
|
| Back to top |
|
|
beerhost
Red Pill
Joined: Apr 27, 2009
Posts: 6
|
| Posted: Wed Apr 29, 2009 7:50 am Post subject: |
|
| Hey James: I thought it had to be something like that. The good news is, I am not very wedded to 2.2.5; I don't really use Excel or NeoOffice a lot. Like many others, I use NeoOffice as an alternative to buying Office. I am extremely grateful that there are hard core people like you and the others who have replied on this topic. Thanks again.Now I just have to upgrade to 3.0! |
|
| Back to top |
|
|
James3359
The Merovingian
Joined: Jul 05, 2005
Posts: 685
Location: North West England
|
| Posted: Wed Apr 29, 2009 7:54 am Post subject: |
|
| Upgrade is probably a good idea, but be aware that there are some changes which users have found problematic. Some of them are listed on this wiki page which I recommend you review before upgrading. |
|
| Back to top |
|
|
beerhost
Red Pill
Joined: Apr 27, 2009
Posts: 6
|
| Posted: Wed Apr 29, 2009 8:00 am Post subject: |
|
| Understood, thanks! I am in there and the forumulas are working. Hoo-ray. |
|
| Back to top |
|
|
|
