To display most repeated text / value from a range of cells

Hi

In my excel sheet i have few results displayed in range (same row different column with few banks cells in between). No i wish to display the most repeated value / result from that range into another cell on a different sheet of the same workbook. Can somebody help please.

Please note that the data (that is displayed in the cell rage from where i wish to get the most repeated one) is a the result of a nested IF statement.
16 minutes ago

I tried

Please Login or Register  to view this content.

but it is displaying #N/A error value but it si not correct as all my celles have result as "A3" displayed after execution of "IF" statement

some one told me to try

Please Login or Register  to view this content.

but its also not working.

MODE is the right function for this if the data is all numeric. Your first formula works for me. That's a pretty short range, too, only 3 cells.

Post up a clear set of data examples and we can look at why it's not working on your data.

If the IF is returning "A3" then the results are non-numeric...

See this thread:

http://www.excelforum.com/excel-gene...xt-string.html

From which Richard Schollar offers:

Please Login or Register  to view this content.

So the MODE is based on an array of MATCH returns for each value in the range... eg {1,2,1} ... whichever is the most common value can be used in an INDEX to return that value... in the above this would be the value in position 1 in the range... ie K6 and I6 are the same (Match returned 1) ... thus return value from I6 as this is most common.

Originally Posted by Kimabdalian

Hi Thanks for your valued feedback, my data is numeric but as result of if statement i am geting a text value displayed in range.

The range provided in my query was just for example

So the values are numeric, but actually text strings, they just look like numbers? Well, here's an interesting twist on the MODE which fixes that...pretty weird:

=MODE((I6:K6)*1)

=INDEX(I6:W6,MATCH(9.99999999999999E+307,I6:W6))
...to find the last numeric value in a range of numeric only values in I6:W6.

=INDEX(I6:W6,MATCH(REPT("z",255),I6:W6))
...to find the last text value in a range of text values I6:W6.

=INDEX(I6:W6,MAX(MATCH(9.99999999999999E+307,I6:W6),MATCH(REPT("z",255),I6:W6)))
...to find the last value in a range with BOTH text and numeric values in I6:W6.

NOTE: That last formula will only work if there is at least one text and one numberic value.

Post up your data, or a usable and clear sample of it. Highlight the cell where you're formula is failing and indicate your desired result (which we know, just want to be totally clear).

GO ADVANCED and click on the paperclip icon to upload a workbook.

Thank JBeaucaire

Please see attached the workbook as per yoru advise.