formula to retrieve numbers counted by other formula

Discuss the spreadsheet application

formula to retrieve numbers counted by other formula

Postby jwaus8823 » Thu Jun 19, 2008 1:19 pm

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
(12.28 KiB) Downloaded 99 times
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

Postby Villeroy » Thu Jun 19, 2008 2:23 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: formula to retrieve numbers counted by other formula

Postby jwaus8823 » Thu Jun 19, 2008 11:57 pm

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

Postby Villeroy » Fri Jun 20, 2008 12:34 am

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
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: formula to retrieve numbers counted by other formula

Postby jwaus8823 » Fri Jun 20, 2008 1:34 am

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

Postby Villeroy » Fri Jun 20, 2008 10:36 am

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
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: formula to retrieve numbers counted by other formula

Postby Villeroy » Fri Jun 20, 2008 11:09 am

I attach a quick proposal on Sheet2, based on what I see on Sheet1.
Attachments
LottoNumbers2.ods
Frequency() for 6 draws
(12.66 KiB) Downloaded 93 times
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
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests