PDA

View Full Version : Solved: "Countif" type application



arrun
12-23-2008, 02:43 AM
Hi, I have two columns like :
-30.300
-66.200
-7.600
-7.300
-33.000


and
-47.687
-44.458
-47.084
-45.715
-44.462


I want to calculate how many times element of 1st column comes lower than corresponding element of 2nd column. In previous example, by manual calculation, that answer is 1.

I have tried with "countif" function, did not get desired answer. What would be the most compact way to do that?

Andy Pope
12-23-2008, 06:06 AM
Assuming your data is in A1:A5 and B1:B5 then this formula returns 1

=COUNTIF(A1:A5,"<" & B1:B5)

What formula are you using?

Mikey
12-23-2008, 07:59 AM
I don't believe you can do it like that, Andy. Try this formula

=SUMPRODUCT((A1:A5<B1:B5)+0)

Andy Pope
12-23-2008, 08:18 AM
My bad. It worked for that set of data only.

GTO
12-23-2008, 01:35 PM
Greetings arrun,

Please excuse me while I (poke my nose in and) try and learn too, as I am awful w/formulas.

Anyone - what was wrong with Andy's at #2? I tried different data and it seemed to always work. What sort of data would make it fail?

Thank you so much,

Mark

Mikey
12-23-2008, 01:50 PM
Hello Mark. The formula

=COUNTIF(A1:A5,"<" & B1:B5)

returns an array of 5 values, the number of values in A1:A5 < B1, then number of values in A1:A5 < B2......then < B3 etc.

In the example given that array is {1;1;1;1;1}

[You can see that if you select the cell with the formula then press F2 key followed by F9 key]

but the formula isn't matching A1 against B1, A2 against B2 etc.

If the values were like this in A1:A5

1,3,1,4,2

...and in B1:B5

3,2,2,3,1,


Then try using that formula in different cells. You'll get different results if you put the formula in D1 or D2 or D5 because the formula returns the result pertaining to the specific row [if you put the formula in D10 it returns 0]

GTO
12-23-2008, 03:15 PM
Hi Mike,

Thank you for the excellent instruction. Took me a bit to put the array part and the 'pertaining to the specific row' part together (my thick skull, not your tutoring), but I followed along and believe I got it. (Well... leastwise til it falls out of my leaky brain.)

Also - Super Cool on the shortcut key combo! I knew F2 of course, but not that a following F9 would change it to the value or array of values. That seems most helpful for testing :-)

Again, my thanks,:bow:

Mark

arrun
12-26-2008, 01:13 AM
Andy's formula rarely works for e.g.

-30.30 -41.47
26.20 -37.48
-7.60 -41.01

=COUNTIF(A1:A3,">" & B1:B3) gives 0 value.

Bob Phillips
12-26-2008, 04:12 AM
It gives 3 for me (as I would expect), but it has already been explained that it is wrong, and an alternative offered.