Finding the appropriate number by setting the frequency of occurrences
- Thread starter bines53
- Start date
Mike H..
Active Member
Hi,
See your workbook with this formula
=INDEX(A1:A30,MATCH(TRUE,FREQUENCY(A1:A30,A1:A30)=F1,0))
Not with AGGREGATE, but you could wrap the whole thing within SUMPRODUCT, like so:
=SUMPRODUCT(LARGE(($A$1:$A$31)*($F$1=FREQUENCY($A$1:$A$30,$A$1:$A$30)),ROWS(G$1:G1)))
SUMPRODUCT handles arrays naturally, hence eliminating the need to explicitly tell XL to handle it like an array.
=SUMPRODUCT(LARGE(($A$1:$A$31)*($F$1=FREQUENCY($A$1:$A$30,$A$1:$A$30)),ROWS(G$1:G1)))
SUMPRODUCT handles arrays naturally, hence eliminating the need to explicitly tell XL to handle it like an array.
The FREQUENCY function gives an output 1 larger than original, because it gives the count of each item <= the bin (items), and 1 final to represent everything larger than last bin. We don't really care about that extra item in array, but we need to make sure we specify a1:a31, otherwise the rows don't match, the formula fails, and XL gives us a sad face. 
NARAYANK991
Excel Ninja
Hi David ,
By now , you must have asked at least 50 questions , all of them having to do with pattern finding amongst numbers , somewhat on the lines of Larry ( Jack-P-Winner ) ; are you any closer to your ultimate objective ?
I find your reluctance to use :
a. Array formulae
b. OFFSET
c. Helper cells / rows / columns
intriguing ; is your data so voluminous , or is it just research on Excel's and forum members' capabilities ?
Your latest question is on the same lines ; if you are willing to use helper columns , you could do what ever that multi-line formula is doing by using simple statements which you must have mastered by now.
Anyway , to answer your question , see the attached file.
If your interest is research , I heartily recommend this amazing blog :
http://excelxor.com
from where this technique has been copied.
Narayan
By now , you must have asked at least 50 questions , all of them having to do with pattern finding amongst numbers , somewhat on the lines of Larry ( Jack-P-Winner ) ; are you any closer to your ultimate objective ?
I find your reluctance to use :
a. Array formulae
b. OFFSET
c. Helper cells / rows / columns
intriguing ; is your data so voluminous , or is it just research on Excel's and forum members' capabilities ?
Your latest question is on the same lines ; if you are willing to use helper columns , you could do what ever that multi-line formula is doing by using simple statements which you must have mastered by now.
Anyway , to answer your question , see the attached file.
If your interest is research , I heartily recommend this amazing blog :
http://excelxor.com
from where this technique has been copied.
Narayan
Hi Narayan,
This forum helped and contributed to me, many.
I always try to improve my workbook .With new functions of Excel, with new ideas, so true that I want to avoid Array formulae,OFFSET ,Helper cells / rows / columns .
I think, my questions all visitors contribute to this wonderful forum.
You know, every question I come here, not in other forums.
If I'm not welcome more here, I will respect your wishes.
Thank you!
David
This forum helped and contributed to me, many.
I always try to improve my workbook .With new functions of Excel, with new ideas, so true that I want to avoid Array formulae,OFFSET ,Helper cells / rows / columns .
I think, my questions all visitors contribute to this wonderful forum.
You know, every question I come here, not in other forums.
If I'm not welcome more here, I will respect your wishes.
Thank you!
David
Last edited:
NARAYANK991
Excel Ninja
Hi David ,
I have absolutely no intention of turning you away from this forum ; I am only intrigued by your questions ; what is the ultimate objective , that is what I was interested in.
All your uploaded files are named test xxxx ; what exactly are they testing ?
Most people approach forums for solutions to problems they face at work ; I am sure that is not so in your case ; or is it ?
Narayan
I have absolutely no intention of turning you away from this forum ; I am only intrigued by your questions ; what is the ultimate objective , that is what I was interested in.
All your uploaded files are named test xxxx ; what exactly are they testing ?
Most people approach forums for solutions to problems they face at work ; I am sure that is not so in your case ; or is it ?
Narayan
Hi Narayan ,
10 years ago (Excel 2003) Excel expert helped me build a system which should help in decision making in trading in the stock market, with a lot of statistics.
The subject was dropped, the main reason it is, the speed did not exist in the
past in Excel,
I decided to work with external software I purchased, I did not want to expose my ideas and gave up the Programmer (C ++).
Two years ago, I came back to take an interest in Excel (2010), Excel 2003 is completely different,
I do everything alone, so as not to reveal the ideas that
(I believe this is something unique)
Name of the game is : speed, and the best formulas.
David
10 years ago (Excel 2003) Excel expert helped me build a system which should help in decision making in trading in the stock market, with a lot of statistics.
The subject was dropped, the main reason it is, the speed did not exist in the
past in Excel,
I decided to work with external software I purchased, I did not want to expose my ideas and gave up the Programmer (C ++).
Two years ago, I came back to take an interest in Excel (2010), Excel 2003 is completely different,
I do everything alone, so as not to reveal the ideas that
(I believe this is something unique)
Name of the game is : speed, and the best formulas.
David
NARAYANK991
Excel Ninja
Hi David ,
Thank you for the explanation ; I respect your wish for secrecy , and wish you all the best.
Narayan
Thank you for the explanation ; I respect your wish for secrecy , and wish you all the best.
Narayan