counting cells that fit criteria

  • Thread starter Thread starter MZ
  • Start date Start date
M

MZ

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.
 
Dave Peterson said:
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
Click to expand...


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]
 
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.


Dave Peterson said:
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
Click to expand...

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]
Click to expand...
 
Dave Peterson said:
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.
Click to expand...

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