MacNN Forums (http://forums.macnn.com/)
-   Applications (http://forums.macnn.com/applications/)
-   -   How do I count the number of matches between columns in NeoOffice? (http://forums.macnn.com/82/applications/462917/how-do-i-count-number-matches/)

 
And.reg Apr 16, 2012 11:12 AM
How do I count the number of matches between columns in NeoOffice?
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.
 
And.reg Apr 20, 2012 01: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?
 
And.reg May 4, 2012 09: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?)
 
P May 4, 2012 10: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.
 
And.reg May 6, 2012 09:08 AM
I know how to do the IF and the sum with COUNTIF... but isn't some cell script that does them combined?
 
cgc May 6, 2012 03: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 May 7, 2012 04: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.
 
cgc May 7, 2012 07:42 AM
Does NeoOffice allow VBA macros? If so, it would be easy to write a macro...prolly take 15 mins tops.
 
And.reg May 9, 2012 10:31 AM
What's a VBA macro?
 
P May 9, 2012 02: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.
 
And.reg May 13, 2012 02: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.
 
cgc May 14, 2012 05: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 May 21, 2012 06: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
 
All times are GMT -4. The time now is 11:15 AM.

Copyright © 2005-2007 MacNN. All rights reserved.
Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2015, vBulletin Solutions, Inc.


Content Relevant URLs by vBSEO 3.3.2