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

Use of asterisk (*) in Calc functions

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


Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

PostPosted: Fri Oct 31, 2008 3:36 am    Post subject: Use of asterisk (*) in Calc functions Reply with quote

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
Back to top
View user's profile Send private message
bobban
OOo Enthusiast
OOo Enthusiast


Joined: 02 Jan 2008
Posts: 172
Location: Australia

PostPosted: Fri Oct 31, 2008 4:00 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
PORTOUX
Newbie
Newbie


Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

PostPosted: Fri Oct 31, 2008 4:24 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
bobban
OOo Enthusiast
OOo Enthusiast


Joined: 02 Jan 2008
Posts: 172
Location: Australia

PostPosted: Fri Oct 31, 2008 4:43 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
PORTOUX
Newbie
Newbie


Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

PostPosted: Fri Oct 31, 2008 6:03 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
bobban
OOo Enthusiast
OOo Enthusiast


Joined: 02 Jan 2008
Posts: 172
Location: Australia

PostPosted: Fri Oct 31, 2008 6:14 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri Oct 31, 2008 8:01 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
PORTOUX
Newbie
Newbie


Joined: 31 Oct 2008
Posts: 4
Location: FRANCE

PostPosted: Fri Oct 31, 2008 9:53 am    Post subject: Reply with quote

The option for regular expressions was not enabled. It enabled it and now it works like a charm. Thanks to all !
Very Happy
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