And again…
---------- Forwarded message ----------
From: Johnny Rosenberg <gurus.knugum@gmail.com>
Date: 2015-11-07 18:39 GMT+01:00
Subject: Re: [libreoffice-users] Need a count formula for calc
To: Robert Peirce <bob@peirce-family.com>
The following works for 10000 rows or less.
{=SUM(IF(A1:A10000>B1:B10000;1;0))}
{=SUM(IF(A1:A10000=B1:B10000;1;0))-SUM(IF(A1:A10000="";1;0))}
{=SUM(IF(A1:A10000<B1:B10000;1;0))}
The exception is if there are rows where the A value is empty and the B
value is not.
For some reason, this didn't work:
{=SUM(IF(A1:A10000=B1:B10000;1;0))-SUM(IF(OR(A1:A10000="";B1:B10000="");1;0))}
I'll see if I can improve this a bit more. Not good enough yet, I think…
Johnny Rosenberg
2015-11-07 18:19 GMT+01:00 Johnny Rosenberg <gurus.knugum@gmail.com>:
2015-11-07 17:44 GMT+01:00 Robert Peirce <bob@peirce-family.com>:
I have two columns of data, A and B. Sometimes A.n>B.n, A.n<B.n or
A.n=B.n. I want to count these occurrences. The closest I could find in
help was countif(), but that doesn't really do what I need. Any ideas?
Yes, a few ideas. I think the best one is using array formulas.
If n is known, this will do the trick (assuming n=1000 in this case):
{=SUM(IF(A1:A1000>B1:B1000;1;0))}
{=SUM(IF(A1:A1000=B1:B1000;1;0))}
{=SUM(IF(A1:A1000<B1:B1000;1;0))}
To enter an array formula, enter the formula as usual but instead of
hitting Enter when done, hit Ctrl+Shift+Enter.
The {} thing is added automatically, you should not enter them manually.
The downside with my example here is when there are empty cells in
A1:B1000. That means that if n is not known, you can't just select a high
number, such as A1:A1000000, because all empty rows will be considered A=B…
I've only been playing with this for a couple of minutes yet so I am not
sure how to solve that, but I'm pretty sure it's possible.
I'll write back if I find a solution to that before anyone else does, but
I'm not sure how much time I will spend on this…
Johnny Rosenberg
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be
deleted
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.