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 count letters rather than numbers

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


Joined: 13 Aug 2005
Posts: 10

PostPosted: Sat Nov 26, 2005 1:21 am    Post subject: How do I count letters rather than numbers Reply with quote

I'm new to this, please help. I'm trying to count the number of times the letter 'w' appears in a column. I can get calc to work with numbers but not letters, Do I use Count? I've tried =counta(d4:28;""w"") which i think is logical but obviously doesnt work
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Sat Nov 26, 2005 1:56 am    Post subject: Reply with quote

Hi Raypalmer,

Try COUNTIF()

BFN Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
Raypalmer
General User
General User


Joined: 13 Aug 2005
Posts: 10

PostPosted: Sat Nov 26, 2005 8:29 am    Post subject: How do I count letters rather than numbers Reply with quote

Cant get that to work either. I fell it must be something to do with the quotes around the letter I'm trying to count. I've tried single quotes, double and no quotes, still cant get it to work
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sat Nov 26, 2005 9:19 am    Post subject: Reply with quote

This will count the number of rows in the column that contain a "w" but if the row contains more than one "w" it will only be counted as 1, e.g., "wayward" will return 1 in the total count.
=COUNTIF(A1:A6;".*W.*")
".*" is a regular expression that means any characters or no characters.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Nov 26, 2005 12:24 pm    Post subject: Reply with quote

Now I'm totally confused. Tell me it's not my age. Sad

I thought I'd fiddle aroud with this since it looks interesting. So, I put "Wayward", no quotes into cell A1. In B1 I tried =COUNTA(A1;"w")

Voila! ...Oops! not quite. I put other words and letteres into more column A cells and then copied the formula down column B. I still got the value "2" I did in the first try! ...even when some A cells were empty!!

What is being calculated here?

David.
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Sat Nov 26, 2005 1:59 pm    Post subject: Reply with quote

David,

You are using CountA which, when viewed in Help, seems a strange function. It returns the number of values in a list so =COUNTA(1;"a";"w";3) returns 4 because we have 4 values in the list we supplied between the parentheses which seems usless unless our list was really long. If you look at your formula it returns 2 because you have supplied a list of 2 values being "A1" and "w", or copy to the next cell down, "A2" and "w".

However help indicates that we can also supply an array or reference instead of the actual values.

So put anything in cells A1-A3, skip A4 and put anything in A5 and A6. In B1 put the formula =COUNTA(A1:A6) and it will return 5 telling us that there is some type of value in 5 out of the 6 cells we gave as an array(?). I guess a "referenrce" would be the name assigned to a group of cells.

I'm glad you asked this as it made me go figure out just what COUNTA did.

I presume you noticed that the formula I gave used CountIF instead of CountA.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Nov 26, 2005 8:40 pm    Post subject: Reply with quote

JohnV wrote:
I presume you noticed that the formula I gave used CountIF instead of CountA.


Thanks. I did. I was just playing around with it, but then had to go out. Just back from an awesome dosplay of opera in a local concert series. If I have to come back again, I want to come back with a voice, and could care less if I didn't know how to turn on a computer.

Thanks again!

David.
Back to top
View user's profile Send private message
Raypalmer
General User
General User


Joined: 13 Aug 2005
Posts: 10

PostPosted: Sun Nov 27, 2005 12:23 am    Post subject: How do I count letters rather than numbers Reply with quote

Sorry but I'm still confused. I can get the formula to count the number of 1's,2's 3's etc in a column using for example =countif(a1:a100;1) but if I replace the one with a it doesnt work. What I am trying to do is get the formula to count the number of (W)ins (D)raws and (L)osses in a list of results for a football team. If I replace W,D, L with numbers eg 1,2,3, it works. Its driving me barmy.
Losing sanity
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Sun Nov 27, 2005 1:21 am    Post subject: Reply with quote

Hi Raypalmer,

Try
=COUNTIF(A$1:A$100;"w.*")

HTH Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
Raypalmer
General User
General User


Joined: 13 Aug 2005
Posts: 10

PostPosted: Sun Nov 27, 2005 1:34 am    Post subject: How do I count letters rather than numbers Reply with quote

Thanks for replying so quickly but unfortunateky that does not work either I get a zero answer rather then the 5 I should get. Cant believe this is so difficult. I've worked around this by giving each letter a numeric value in a separate column and used my formula on this new column. It means typing data in twice but thats life. So unless you can come up with any other suggestions I guess I'm stuck with it. Appreciate your help
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Sun Nov 27, 2005 5:50 am    Post subject: Reply with quote

Hi Raypalmer,

The above WFM, I tested on a column of text containing various Win, Loss and Draw entries.

Did you copy and paste the above or retype it? I can only assume that there's something about the way you're entering the formula, or perhaps some strange configuration setting that's preventing it from working correctly for you.

Perhaps you could upload a copy of your file on our forums http://www.8daysaweek.co.uk/forums/ then we could have a look at what the problem is?

BFN Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Nov 27, 2005 7:23 am    Post subject: Reply with quote

8daysaweek.co.uk wrote:

Did you copy and paste the above or retype it?


I'm on the way out, aand will be looking at this interesting problem later, but for now, I DID copy/paste, and it works, as do others, but does not include "Wayward", as had been already suggested. Sorry for the rushed reply, but I'll be innerested in other results later on. LAte night opera last night, out on business today, and then a Chopin concert this afternoon. Not bad for being retired in a rural area. Smile

David.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Nov 27, 2005 8:56 am    Post subject: Reply with quote

The formula provided by 8daysaweek.co.uk should work if the option "Enable Regular Expressions in Formals" is checked.
See Tools > Options > Calc > Calculation
Back to top
View user's profile Send private message
Raypalmer
General User
General User


Joined: 13 Aug 2005
Posts: 10

PostPosted: Sun Nov 27, 2005 9:34 am    Post subject: Reply with quote

Thanks guys, you've saved my sanity. Following Villeroys suggestion about amending ''enable Regular Expressions in Formals'' (whatever that means) my formula now works. Thanks everyone for your patience
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Nov 27, 2005 10:07 am    Post subject: Reply with quote

Villeroy wrote:
The formula provided by 8daysaweek.co.uk should work if the option "Enable Regular Expressions in Formals" is checked.
See Tools > Options > Calc > Calculation


Thanks.

David.
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