In using the LARGE and SMALL function, how can I show non repeating numbers?
Example in Column H, it shows 40,37,35,35,33 and Column K and L also repeats same numbers. I just want them to list only once in order to have 5 different values.
I can’t see how I can do that. Thanks for any suggestion or help.
FreqValue-Count.ods (23.0 KB)
Start in G2 with:
=MAX(B$2:B$31)
in G3, followed by:
=MAXIFS(B$2:B$31;B$2:B$31;"<"&G2)
»pull down« G3
Thank you, it work fine.
For the bottom values I just replaced =MAX & =MAXIFS with =MIN & =MINIFS.
obviously! but dont forget:
"<" ⇒ ">"One last needed help, how do I get the "results # next to the max value? In other words the reference number to the max number.
Thanks for all the help again.
FreqValue-Count-B.ods (23.2 KB)
PKG, Thank You and to karolus. This has answered all my question and really exposed me to commands I never knew. To you both have a good week.
dave
Hey Guys, I wished I was finish, but I could not get “=TEXTJOIN” function to work in my actual large spreadsheet (seven years of data). Is there a simple way around this? Or do I have to create a new column and convert the text numbers into real numbers? My problem is in the 4 columns (blank) called “Results # Drawn”.
Again, thanks for all your help.
Result # ERROR.ods (76.1 KB)
with the text “Results # Drawn”.
Formula for Q3:
=TEXTJOIN(", ";1;IF($C$3:$G$1000=R3;ROW($A$3:$A$1000);""))
array! enter it with ctrl ⇑ enter
and hold the ctrlkey meanwhile pulling down
these are the numbers of the rows where the value from R3 occurs in C3:G1000
For Example the »66« is the first time in F8 the next in G34 and so on…
thanks, I get it now.
