Welcome to the MacNN Forums.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

You are here: MacNN Forums > Software - Troubleshooting and Discussion > Applications > How do I count the number of matches between columns in NeoOffice?

How do I count the number of matches between columns in NeoOffice?
Thread Tools
Mac Elite
Join Date: Feb 2004
Location: Excellent, the sports issue is within arm's reach, I'll be here all day.
Status: Offline
Reply With Quote
Apr 16, 2012, 12:12 PM
 
Greetings.
I would normally ask this question in a NeoOffice forum, but they want $100 donation, and I'm not doing that.

I need to count the number of matching entries in two separate columns and score them. Here's an example:

Code:
KEY PAR1 PAR2 A D A C C D C C A A B B B A B D C C B A C D D B SCORE 37.5 25
PAR1 stands for participant 1.

What script do I need to write in the cells that go where the "37.5" and "25" go?
It's just hard for me to search for this kind of thing (e.g. COUNTIF, DCOUNT, etc.), because I've tried various phrases and I just get random results.
This one time, at Boot Camp, I stuck a flute up my PC.
     
And.reg  (op)
Mac Elite
Join Date: Feb 2004
Location: Excellent, the sports issue is within arm's reach, I'll be here all day.
Status: Offline
Reply With Quote
Apr 20, 2012, 02:59 PM
 
[4 days later]

Greetings again.

I have not yet received an answer to my question, and I expected that this would be a simple little thing.

How would I do this in a single cell (per participant) in NeoOffice?
This one time, at Boot Camp, I stuck a flute up my PC.
     
And.reg  (op)
Mac Elite
Join Date: Feb 2004
Location: Excellent, the sports issue is within arm's reach, I'll be here all day.
Status: Offline
Reply With Quote
May 4, 2012, 10:34 AM
 
I still haven't received an answer. How do I do this in NeoOffice?

(If not NeoOffice, then how do you do it at least in Excel?)
This one time, at Boot Camp, I stuck a flute up my PC.
     
P
Moderator
Join Date: Apr 2000
Location: Gothenburg, Sweden
Status: Offline
Reply With Quote
May 4, 2012, 11:34 AM
 
I don't use Neooffice, but you can always cheat. Add an extra column after PAR1 and one after PAR2. In that column, put a simple IF like

IF(B3=A3;1;0)

and then do a simple sum of that column.

COUNTIF in Excel is an absolute biotch of a command to use, because you have to escape the criteria but if you compare to another cell, you must not escape the cell. Like this:

=COUNTIF(A1:A5,">"B1)

The help file does not mention this, and in fact implies the opposite.
The new Mac Pro has up to 30 MB of cache inside the processor itself. That's more than the HD in my first Mac. Somehow I'm still running out of space.
     
And.reg  (op)
Mac Elite
Join Date: Feb 2004
Location: Excellent, the sports issue is within arm's reach, I'll be here all day.
Status: Offline
Reply With Quote
May 6, 2012, 10:08 AM
 
I know how to do the IF and the sum with COUNTIF... but isn't some cell script that does them combined?
This one time, at Boot Camp, I stuck a flute up my PC.
     
cgc
Professional Poster
Join Date: Mar 2003
Location: Down by the river
Status: Offline
Reply With Quote
May 6, 2012, 04:13 PM
 
I consider myself an Excel guru but without resorting to either a hidden row with the row-by-row comparison result in it as P suggested or using VBA I don't see how it's possible.
     
P
Moderator
Join Date: Apr 2000
Location: Gothenburg, Sweden
Status: Offline
Reply With Quote
May 7, 2012, 05:25 AM
 
It may be possible using the structured references in Excel 2007 or later, but I'm not sure that it can, and anyway I wouldn't know how to do it NeoOffice.
The new Mac Pro has up to 30 MB of cache inside the processor itself. That's more than the HD in my first Mac. Somehow I'm still running out of space.
     
cgc
Professional Poster
Join Date: Mar 2003
Location: Down by the river
Status: Offline
Reply With Quote
May 7, 2012, 08:42 AM
 
Does NeoOffice allow VBA macros? If so, it would be easy to write a macro...prolly take 15 mins tops.
     
And.reg  (op)
Mac Elite
Join Date: Feb 2004
Location: Excellent, the sports issue is within arm's reach, I'll be here all day.
Status: Offline
Reply With Quote
May 9, 2012, 11:31 AM
 
What's a VBA macro?
This one time, at Boot Camp, I stuck a flute up my PC.
     
P
Moderator
Join Date: Apr 2000
Location: Gothenburg, Sweden
Status: Offline
Reply With Quote
May 9, 2012, 03:43 PM
 
Visual Basic, the built-in macro language used in Excel and many other MS products. It's a scripting language, so you could easily set up something like a for block to iterate over the elements.

But actually, I figured out how to use do this in Excel. If Key is column A, Par 1 is column B, and the values are from rows 3 through 10, you type the following to do the math for Par 1:

=SUM(IF(B3:B10=A3:A10,1,0))

and hold command when you press enter (control+shift if you're on Windows). This will tell Excel to treat the math as an array formula, which does what you'd like. The logic is that the IF function returns an array with 1 where the two columns match and 0 otherwise, and the outer SUM will just sum up the elements of the array. You can use AVERAGE instead if you like - that's closer to the example you have above. No idea if NeoOffice can do that, though.
( Last edited by P; May 9, 2012 at 03:54 PM. )
The new Mac Pro has up to 30 MB of cache inside the processor itself. That's more than the HD in my first Mac. Somehow I'm still running out of space.
     
And.reg  (op)
Mac Elite
Join Date: Feb 2004
Location: Excellent, the sports issue is within arm's reach, I'll be here all day.
Status: Offline
Reply With Quote
May 13, 2012, 03:53 PM
 
Well it was a nice shot, but even with the Command-Enter, I get either a "Err:508" message or #VALUE! message in NeoOffice. I tried alternating the : and commas around, and trying different combinations of parentheses, but I got the same cell message.
( Last edited by And.reg; May 13, 2012 at 04:01 PM. )
This one time, at Boot Camp, I stuck a flute up my PC.
     
cgc
Professional Poster
Join Date: Mar 2003
Location: Down by the river
Status: Offline
Reply With Quote
May 14, 2012, 06:12 PM
 
Tried to DL NeoOffice to try it out but they require a "donation". Works fine in Excel (attached example)...also works great in OpenOffice 3.2 (via Linux).
     
And.reg  (op)
Mac Elite
Join Date: Feb 2004
Location: Excellent, the sports issue is within arm's reach, I'll be here all day.
Status: Offline
Reply With Quote
May 21, 2012, 07:10 PM
 
NeoOffice downloads

Try NeoOffice 3.1.2 in the downloads page.

Also, the link to your attached example doesn't work, I get the following error message in Firefox:

The connection was reset
This one time, at Boot Camp, I stuck a flute up my PC.
     
   
Thread Tools
Forum Links
Forum Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Top
Privacy Policy
All times are GMT -4. The time now is 04:05 AM.
All contents of these forums © 1995-2014 MacNN. All rights reserved.
Branding + Design: www.gesamtbild.com
vBulletin v.3.8.8 © 2000-2014, Jelsoft Enterprises Ltd., Content Relevant URLs by vBSEO 3.3.2