Mann-Whitney U test

  • Thread starter Thread starter Rocky4460
  • Start date Start date
R

Rocky4460

Can anyone suggest a formula for this?
Basically, I'm trying to find out how many times does a number on the data 1
column exceed the numbers on the data 2 column.

Data 1 Data 2 Value
4 3
5 18
10 1
4 2

The result should say for the 'value' column 3, since 4 >3,1,2 but less than
18
similiarly for 5, the result should be 3 and so on.

Thanks, I tried the countif method, but does not help.
 
Hi,

Assuming you are starting in row 1 put this in c1 and drag down to solve for
each value in Column A

=COUNT(IF(A1>$B$1:$B$10,$B$1:$B$10,FALSE))

It's an array formula so coommit with Ctrl+Shift+Enter NOT just enter.

Mike
 
This formula does what you want:

=SUMPRODUCT(--(A2>B$2:B$5))

where the heading Data1 is in A1, Data2 in B1 and Value in C1 - put
the formula in C2, adjust the ranges to suit your real data, then copy
down.

Hope this helps.

Pete
 
Thanks, Mate. It worked.

It also worked with =COUNTIF(B$1:B$4,"<=",&A1)

I'd like to learn more of SumProduct function.

Can you explain what SumProduct actually does and what the two hyphenes are?

Appreciate it.
 
Glad it worked for you.

The definitive guide to SUMPRODUCT is on Bob Phillips' site:

http://www.xldynamic.com/source/xld.html

However, the site seems to be down at the moment, so maybe try again
later.

The double unary minus -- converts the TRUE and FALSE into 1 and 0 for
each cell in the range, and these then get added together.

Hope this helps.

Pete
 
Back
Top