## formula to retrieve numbers counted by other formula

### formula to retrieve numbers counted by other formula

I will try to describe my problem. I used a formula: count if; to count occurrences in a column of numbers and match them to a set of numbers from 1-54. Then I used a formula: max; to count the highest frequencies of those occurrences. What I need now is a formula that retrieves the exact numbers out of the 1-54 group that occurred the most in the count by my other formulas. I have read a book on formulas for Microsoft Excell that uses a generic type of example and I am having trouble matching my exact data / arguments / criteria to the generic syntax of the formula.
Attachments
Lotto Numbers as of 6-13-08.ods
OOo 2.4.X on Ms Windows XP + none
jwaus8823

Posts: 7
Joined: Thu Jun 12, 2008 1:36 am

### Re: formula to retrieve numbers counted by other formula

Before you try to retrieve anything from M2:BN2 you should check if that range actually does what you want. I don't see the purpose of the COUNTIF series.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: formula to retrieve numbers counted by other formula

Is your answer saying that the countif forula is redundant in the presence of the max formula?
OOo 2.4.X on Ms Windows XP + none
jwaus8823

Posts: 7
Joined: Thu Jun 12, 2008 1:36 am

### Re: formula to retrieve numbers counted by other formula

Put the cursor in cell BN2. I'm almost shure, it does not do what you want it to do.

What do you want? The frequency of each number?
=COUNTIF(\$E\$2:\$J\$49;ROW(\$A\$1:\$A\$54)) [Ctrl+Shift+Enter]
returns the frequencies for numbers 1...54
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: formula to retrieve numbers counted by other formula

The 0 value in cell BN2 is correct since the #54 does not occur in the column E2 through E49. It is my intent to find which numbers occur most frequently in each column singularly. My question as originally posted was to learn how to get the answer of which number / numbers occurred the most frequently in each column considered singularly to be posted in a cell without my manually counting and posting it / them.
OOo 2.4.X on Ms Windows XP + none
jwaus8823

Posts: 7
Joined: Thu Jun 12, 2008 1:36 am

### Re: formula to retrieve numbers counted by other formula

Sorry, I was puzzled by the ":BN\$1" in =COUNTIF(\$E\$2:\$E\$49;M\$1:BN\$1). You can remove that part unless you want to use an array formula. In linear context Calc ignores it anyway.
So M2 counts how many times there is M1 (value 1) in E2:E49. First column E only means, you are interested in the first drawn number in all drawings. I can imagine that you are after functions like SMALL, LARGE and FREQUENCY.
I have read a book on formulas for Microsoft Excell that uses a generic type of example

And the example is ...?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: formula to retrieve numbers counted by other formula

I attach a quick proposal on Sheet2, based on what I see on Sheet1.
Attachments
LottoNumbers2.ods
Frequency() for 6 draws
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany