counting cells that fit criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MZ - 25 Jun 2007 00:48 GMT

I'm trying to count the number of columns that contain values where one row
is greater than the other. For instance,

Data1 5 8 9 3
Data2 16 81 49 1

I want to count the number of times the Data1 column squared is greater than
the Data2 column. The answer of course should be 3. I'd like to avoid
creating a third row to specifically perform the comparison and then just
summing that third row. No new rows!

This seems like a trivial problem, but I haven't been able to get it to
work.

Reply to this Message

Dave Peterson - 25 Jun 2007 01:04 GMT

One way (with the data in B1:E2):

=SUMPRODUCT(--(((B1:E1)^2)>(B2:E2)))

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

> I'm trying to count the number of columns that contain values where one row
> is greater than the other. For instance,
[quoted text clipped - 9 lines]
> This seems like a trivial problem, but I haven't been able to get it to
> work.

Signature

Dave Peterson

Reply to this Message

MZ - 25 Jun 2007 01:24 GMT

> One way (with the data in B1:E2):
>
[quoted text clipped - 8 lines]
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html

Thanks. Now what if I wanted to use the TINV operation in the comparison?

For example, I want to compare one row of numbers with a TINV computation
based on another row of numbers. I type in the following:

=SUMPRODUCT(--((worksheet1!R72C2:R72C199)>TINV(2*Alpha,(worksheet1!R14C2:R14
C199) *2 - 2)))

...and it doesn't work. I get #NUM!. I assume it's because the TINV
command isn't returning an array of numbers? [Alpha is defined already as a
"name", worksheet1 contains the data, row 72 contains calculated t-values,
and row 14 contains number of trials]

Reply to this Message

Dave Peterson - 25 Jun 2007 01:42 GMT

I put a small amount of test data in a worksheet and used a simplified version
of your formula (in A1 reference style) and it worked fine for me:

=SUMPRODUCT(--((B3:E3)>TINV(0.5,B2:E2)*2-2))

I'd check your input once more.

Maybe even simplify the formula with smaller amount of data and see what
happens.

> > One way (with the data in B1:E2):
> >
[quoted text clipped - 22 lines]
> "name", worksheet1 contains the data, row 72 contains calculated t-values,
> and row 14 contains number of trials]

Signature

Dave Peterson

Reply to this Message

MZ - 25 Jun 2007 01:52 GMT

> I put a small amount of test data in a worksheet and used a simplified version
> of your formula (in A1 reference style) and it worked fine for me:
[quoted text clipped - 5 lines]
> Maybe even simplify the formula with smaller amount of data and see what
> happens.

Works! Problem was that one of the columns in the middle of the data was
empty. Thanks for your help.

Reply to this Message

Rate this thread: