Finding maximum value in a range:highest value in columns

[sats43]

Hi,

I need to find the most repeated highest value in columns, is there any such formulea, i tried with IF, but its too complicated.

The data looks like this.

C1 C2 C3 C4 C5 C6 C7 C8
1 2 2 1 1 3 3 2

actual result i am expecting from this is -- 2

[squiggler47]

try this :-

=MAX((COUNTIF(C1:C8,C1:C8)=MAX(COUNTIF(C1:C8,C1:C8)))*C1:C8)

its an array formula so to enter it press CTRL-SHIFT-ENTER rather than just enter

[pike]

or maybe
=MODE(C1:C8)

[sats43]

Thanks Squiggler, its working perfectly :-)

[squiggler47]

Since there are 3 x 1 and 3 x 2 mode would return 1, rather than the requested maximum value of 2!

[pike]

true, I missed that

[squiggler47]

I wasnt happy with the first formula, I dont like too many references to the same cells so here is attempt 2 which cuts it from 5 references to 2!

=SUMPRODUCT(MODE(LARGE(I6:L6,ROW(INDIRECT("1:"&COUNT(I6:L6))))))

or

=MODE(LARGE(I6:L6,ROW(INDIRECT("1:"&COUNT(I6:L6)))))

the second to be entered with CTRL-SHIFT-ENTER!

[venu_creative]

With Regards