Mode

[rossmolden]

Thank you, MODE is definately the right function, however I think I
need to incorporate IF also, but not sure how.

Using the below example, I would want to find out what appears the
most (MODE) with the number 1 in the same row (IF). The answer is (2)
as it appears with 1 in the same row twice. I'm not sure how to put
this all together though!

4 1 3 2
2 1
4 3 2
1
2 3 4 1

 

[Max]

Assume source data as posted within A15
In F1: =IF(OR(A1={"",1}),"",IF(COUNTIF($A1:$D1,1),A1,""))
Copy F1 across to I1, fill down to I5. This creates the criteria matrix
which isolates only the rows containing "1" (the key association criteria),
and simultaneously cleans off blank source cells and the key 1's in the
resulting matrix.

Then in J1: =MODE(F1:I5)
nails the result. Lightly tested, J1 seems to return correct results. In the
event of ties, it'll return the num which appears "first", ie leftmost in
row, from top row down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik