how find max data in range in excel
 

sir,
i have 2nd list like
A B C
1 rajesh kishor narendra
2 rajesh kishor narendra
3 narendra narendra narendra
4 Result will be here i.e. narendra


and i want to find above maximum name with result max time


plese help me

thanking you

how find max data in range in excel
 

One thought is to re-lay the multi col data into a column range first,
then apply an index n match to extract the name with the max counts

Assume your 3 col source data as posted is in A1:C3
Put in say, E1:
=OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy E1 down to E9, this re-lays the source data into a col range

Then put in F1, array-enter the formula,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(E1:E9,MATCH(MAX(COUNTIF(E1:E9,E1:E9)),COUNT IF(E1:E9,E1:E9),0))
F1 will return the name with the max counts within E1:E9. In the event of
any ties in the max counts, then the name (amongst the ties) which appears
first, ie higher up in E1:E9 will be returned.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"how findout max data like text, any word" wrote:
A B C
1 rajesh kishor narendra
2 rajesh kishor narendra
3 narendra narendra narendra
4 Result will be here i.e. narendra

and i want to find above maximum name with result max time

how find max data in range in excel
 

Another way to do it (array formula, validate with Ctrl+Shift+Enetr) :
=INDEX(A1:C3,MAX((IF(COUNTIF(A1:C3,A1:C3)=MAX(COUN TIF(A1:C3,A1:C3)),1,0)*ROW(A1:C3))),MAX((IF(COUNTI F(A1:C3,A1:C3)=MAX(COUNTIF(A1:C3,A1:C3)),1,0)*COLU MN(A1:C3))))
HTH
Daniel

One thought is to re-lay the multi col data into a column range first,
then apply an index n match to extract the name with the max counts

Assume your 3 col source data as posted is in A1:C3
Put in say, E1:
=OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy E1 down to E9, this re-lays the source data into a col range

Then put in F1, array-enter the formula,
ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(E1:E9,MATCH(MAX(COUNTIF(E1:E9,E1:E9)),COUNT IF(E1:E9,E1:E9),0))
F1 will return the name with the max counts within E1:E9. In the event of
any ties in the max counts, then the name (amongst the ties) which appears
first, ie higher up in E1:E9 will be returned.

how find max data in range in excel
 

Another way to do it (array formula, validate with Ctrl+Shift+Enetr) :
=INDEX(A1:C3,MAX((IF(COUNTIF(A1:C3,A1:C3)=MAX(COUN TIF(A1:C3,A1:C3)),1,0)*ROW(A1:C3))),MAX((IF(COUNTI F(A1:C3,A1:C3)=MAX(COUNTIF(A1:C3,A1:C3)),1,0)*COLU MN(A1:C3))))
HTH
Daniel

sir,
i have 2nd list like
A B C
1 rajesh kishor narendra
2 rajesh kishor narendra
3 narendra narendra narendra
4 Result will be here i.e. narendra


and i want to find above maximum name with result max time


plese help me

thanking you

how find max data in range in excel
 

for small datasets you might use a workaround

replace all rajesh with 1
replace all kishor with 2
replace all narendra with 3

and use MODE function


On 17 Sty, 12:19, how findout max data like text, any word
osoft.com wrote:
sir, * * * * * *
i have 2nd list like * * * * * *
* * * * * * * A * * * * * * * * * * B * * * * * * * *C * * * * *
1 * * *rajesh * kishor *narendra
2 * * *rajesh * kishor *narendra
3 * * *narendra narendra * * * *narendra
4 * * *Result will be here i.e. narendra * * * * * * * *

and i want to find above maximum name with result max time * * * * * * *

plese help me * * * * *

thanking you

how find max data in range in excel
 

Sorry, wrong place to post.
Daniel

Another way to do it (array formula, validate with Ctrl+Shift+Enetr) :
=INDEX(A1:C3,MAX((IF(COUNTIF(A1:C3,A1:C3)=MAX(COUN TIF(A1:C3,A1:C3)),1,0)*ROW(A1:C3))),MAX((IF(COUNTI F(A1:C3,A1:C3)=MAX(COUNTIF(A1:C3,A1:C3)),1,0)*COLU MN(A1:C3))))
HTH
Daniel

One thought is to re-lay the multi col data into a column range first, then
apply an index n match to extract the name with the max counts

Assume your 3 col source data as posted is in A1:C3
Put in say, E1:
=OFFSET($A$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy E1 down to E9, this re-lays the source data into a col range

Then put in F1, array-enter the formula, ie press CTRL+SHIFT+ENTER to
confirm the formula:
=INDEX(E1:E9,MATCH(MAX(COUNTIF(E1:E9,E1:E9)),COUNT IF(E1:E9,E1:E9),0))
F1 will return the name with the max counts within E1:E9. In the event of
any ties in the max counts, then the name (amongst the ties) which appears
first, ie higher up in E1:E9 will be returned.

how find max data in range in excel
 

Tested your array formula with the sample data below,
where the answer should be: rajesh
but it continued to return: narendra

rajesh kishor kishor
rajesh kishor rajesh
narendra rajesh narendra

Any clues ?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Daniel.C" wrote:
Another way to do it (array formula, validate with Ctrl+Shift+Enetr) :
=INDEX(A1:C3,MAX((IF(COUNTIF(A1:C3,A1:C3)=MAX(COUN TIF(A1:C3,A1:C3)),1,0)*ROW(A1:C3))),MAX((IF(COUNTI F(A1:C3,A1:C3)=MAX(COUNTIF(A1:C3,A1:C3)),1,0)*COLU MN(A1:C3))))