Finding the number corresponding to the number of occurrences

[bines53]

Hello friends !

range 20X20 ,number of occurrences known column X, I need to find what is the number corresponding to the number of occurrences.
Y column appears in the desired number.

Thank you !

 

[Somendra Misra]

Hi David,

Try result of column Z (yellow colour)

Regards,

 

[bines53]

Hi Somendra ,

Excellent solution!

If possible using the help column, avoid from CSE and INDIRECT function ?

Thank you !

 

[Somendra Misra]

@bines53

Will have to try.

Regards,

 

[bines53]

Perhaps these data, it is possible to build a trick ?
190 has a range of values,
24/190=12.63%

 

[John Jairo V]

Hi @bines53 and @Somendra Misra.

I leave another option (array formula without INDIRECT) in the file. Blessings!

 

[bines53]

Hi John,

Beautiful !!!

I was able to convert it to a function with AGGREGATE .

=IFERROR(AGGREGATE(15,6,$B$2:$U$21/(COUNTIF($B$2:$U$21,$B$2:$U$21)=X4),1+SUMIF(X$3:X3,X4)),"")

I think you made a revolution !!!

Thank you !

 

[John Jairo V]

Yep!

This is the formula without CSE and AGGREGATE (Excel 2010 and above):

=AGGREGATE(15,6,$B$2:$U$21/(COUNTIF($B$2:$U$21,$B$2:$U$21)=X4),1+SUMIF(X$3:X3,X4))

Blessings!