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

Author Message
PORTOUX
Newbie

Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

 Posted: Fri Oct 31, 2008 3:36 am    Post subject: Use of asterisk (*) in Calc functions I can't figure out how to make the asterisk work in this simple Calc function the way it does in Excel. =SUM.IF(A1:A9;B2&C2&"*";D1:D9) Can someone help me out with this, it's driving me crazy ! Thanks Portoux
bobban
OOo Enthusiast

Joined: 02 Jan 2008
Posts: 172
Location: Australia

 Posted: Fri Oct 31, 2008 4:00 am    Post subject: hi PORTOUX, I think you just want this: =SUM.IF(A1:A9;B2&C2&".*";D1:D9) Excel uses wildcards I believe, which means * is just a replacement for anything. OO uses regular expressions._________________nil sine labore
PORTOUX
Newbie

Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

 Posted: Fri Oct 31, 2008 4:24 am    Post subject: I already tried this before and it doesn't work. Thank you though, any other ideas ? That's right * is just a replacement for anything in Excel.
bobban
OOo Enthusiast

Joined: 02 Jan 2008
Posts: 172
Location: Australia

 Posted: Fri Oct 31, 2008 4:43 am    Post subject: Please explain your purpose more. I thought you were looking for words in column A, that are the concatenation of the strings in B2 and C2, with any number of extra characters following. So if: B1 = "h" C1= "o" then searching for B2&C2&".*", means "ho*" where * can be any number of extra characters. ie. A1 = "hope" - yes A1 = "holly" - yes A1 = "ho" - yes A1 = "half" - no Here is the file I just experimented with. Have a look and see if this is what you want. http://www.4shared.com/file/69131021/737c5225/Sumif_and__regex.html_________________nil sine labore
PORTOUX
Newbie

Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

 Posted: Fri Oct 31, 2008 6:03 am    Post subject: That is exactly what I am trying to do and you are right, it works if I do it from scratch in a new .ods file. BUT it does not work when I convert an excel file to .ods file and modify the formula in Calc. Any idea why that is ? It is just that I already have this big excel spreadsheet that I would like to convert to Calc without having to start everything from scratch. Thank you very much for all the help.
bobban
OOo Enthusiast

Joined: 02 Jan 2008
Posts: 172
Location: Australia

 Posted: Fri Oct 31, 2008 6:14 am    Post subject: Ahhh, interesting. There is some funny business that happens when xls is converted to ods. This thread might be your answer courtesy of Villeroy: http://www.oooforum.org/forum/viewtopic.phtml?t=55549&highlight=xls+ods+regular Excel does not user regular expressions, so when OO opens an XLS file it assumes that it does not have regular expressions. Perhaps even after converting to ODS the option for regular expressions is not enabled. Check it here: Tools->Options->OpenOffice.org Calc->Calculate->"Enable regular expressions in formulas"_________________nil sine labore
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Fri Oct 31, 2008 8:01 am    Post subject:

 bobban wrote: hi PORTOUX, I think you just want this: =SUMIF(A1:A9;B2&C2&".*";D1:D9) [Edited: no period in SUMIF] Excel uses wildcards I believe, which means * is just a replacement for anything. OO uses regular expressions.

This is on track. But first go to Tools/Options/Calc, and in Calculate, toggle on Enable Regular Expressions.

David.
PORTOUX
Newbie

Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

 Posted: Fri Oct 31, 2008 9:53 am    Post subject: The option for regular expressions was not enabled. It enabled it and now it works like a charm. Thanks to all !
 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