zoom38

02-15-2007, 05:23 PM

I have a column which has the number of cells that contain specific data.

Im using the following formulas to get the top 5 values that occur most frequently.

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,1),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,2),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,3),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,4),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,5),$F$3:$F$7,0))

My problem is that if there are more than one value that occurs the same amount of times, the formulas will only list the first value.

ex: I have the following top 5 values in column F

17

18

15

17

8

The corresponding values to the above in column E are:

red

white

blue

yellow

pink

My formulas will return white, red, red, blue, pink.

The formulas will only return the corresponding value for the first instance of 17 which is red. Yellow which is also 17 gets skipped. How can I avoid this?

Im using the following formulas to get the top 5 values that occur most frequently.

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,1),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,2),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,3),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,4),$F$3:$F$7,0))

= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,5),$F$3:$F$7,0))

My problem is that if there are more than one value that occurs the same amount of times, the formulas will only list the first value.

ex: I have the following top 5 values in column F

17

18

15

17

8

The corresponding values to the above in column E are:

red

white

blue

yellow

pink

My formulas will return white, red, red, blue, pink.

The formulas will only return the corresponding value for the first instance of 17 which is red. Yellow which is also 17 gets skipped. How can I avoid this?