[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
tomas3
General User

Joined: 05 Nov 2005
Posts: 6

 Posted: Sat Nov 05, 2005 5:55 pm    Post subject: Make a Histogram How do I make a histogram? I need to determine the frequency of many eight digit numbers. I have to know the amount of each specific number so bins will not work... Any ideas?? This is really where I am running into a problem. I have already gotten a few histogram/stat add-ons... Using {=FREQUENCY(A8:A34.C8:C14)} .. A's being the input and C's being the "bins" .. works but I have to add the new bins manually. I figured there has to be a better way. THANKS
RonIA
Super User

Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

 Posted: Sat Nov 05, 2005 6:45 pm    Post subject: Not an expert, but I do believe database functionality would be more in line with the type of calculation you are using. If you put your data into Base you could do something like... SELECT "MyNum", COUNT( "MyNum" ) AS "CountOf" FROM "Table2" "Table2" GROUP BY "MyNum" ORDER BY "MyNum" ASC This selects the values, counts how many of each there are and displays the count as a column CountOf. There may very well yet be a better way, but this should work._________________Ron from Iowa, USA
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Sat Nov 05, 2005 6:56 pm    Post subject: Re: Make a Histogram

 tomas3 wrote: How do I make a histogram? I need to determine the frequency of many eight digit numbers. I have to know the amount of each specific number so bins will not work... Any ideas?? This is really where I am running into a problem. I have already gotten a few histogram/stat add-ons... Using {=FREQUENCY(A8:A34.C8:C14)} .. A's being the input and C's being the "bins" .. works but I have to add the new bins manually. I figured there has to be a better way. THANKS

You will have to define ranges for your numbers.

I tried this for example with numbers to 20, and chosen ranges in multiples of 4. I've not done it before, so it's a bit meagre.

Data in column A.

In B1 put =IF(A1>16;1;IF(A1>12;2;IF(A1>8;3;IF(A1>4;4;5))))

Copy down the column however far you need. I did down to row 20. You might name selections though for easier programming/editing.

Now, in D3, put =COUNTIF(B1:B20;5)
In D4 put =COUNTIF(B1:B20;4)
In D5 put =COUNTIF(B1:B20;3)
In D6 put =COUNTIF(B1:B20;2)
In D7 put =COUNTIF(B1:B20;1)

Use D3:D7 for the histogram.

David.
tomas3
General User

Joined: 05 Nov 2005
Posts: 6

 Posted: Sat Nov 05, 2005 9:13 pm    Post subject: Yeah, I think Base is going to be the way to go. I am organizing a number of product ID numbers they are all eight digitals long, and there are a few hundred different products. I want to try to avoid having to put each in manually and they doing the histogram from there, otherwise the COUNT IF would work... Thanks!
RonIA
Super User

Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

 Posted: Sun Nov 06, 2005 5:07 am    Post subject: Yep, Base is the way to go, you don't have to define new items in "bins" as they are added, they will just appear as they are added to the data. Good to have Base added to OOo._________________Ron from Iowa, USA
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Sun Nov 06, 2005 10:50 am    Post subject: Re: Make a Histogram

 tomas3 wrote: How do I make a histogram? I need to determine the frequency of many eight digit numbers. I have to know the amount of each specific number so bins will not work... Any ideas?? This is really where I am running into a problem. I have already gotten a few histogram/stat add-ons... Using {=FREQUENCY(A8:A34.C8:C14)} .. A's being the input and C's being the "bins" .. works but I have to add the new bins manually. I figured there has to be a better way. THANKS

I fully agree with a database-solution.
Here is just another one:
Define database range "input" \$A\$8:\$A\$65536 with/without headers
Define database range "filter_out" \$B\$8 with/without headers
Both db-ranges with the "insert cells"-option checked.
Cell C8: = COUNTIF(input;B
Select database range "input" or a single cell within.
Data > Filter > Standard Filter: "Column A", "=", "not empty", output-range:"B8",persistant = checked, no dupes = checked.
Range "filter_out" will be extended and the formula in C8, too.
When you are ready with entering data: Data > Refresh Range
How do I know when I have to refresh?
Define a flashy cell-style (eg. something red with name "ErrNA")
Select "input"
Format > Conditional: Condition1.Formula: =AND(ISNUMBER(A;ISNA(MATCH(A8;filter_out;0)))
Condition1.Style: ErrNA
When you get a red cell after input, then you know you have to refresh the filter of your database range. Database-ranges remember last filter and sort order.
tomas3
General User

Joined: 05 Nov 2005
Posts: 6

 Posted: Sun Nov 06, 2005 8:17 pm    Post subject: My knowledge of databases is extremely limited so I have an spreadsheet using the FREQUENCY function, which does not work especially well, but I am going to be looking further into this... In the meantime I thought of this (for you linux guys): sort myNumbers.txt | uniq -c > myNumbers2.txt OR sort myNumbers.txt | uniq -c these commands will output all the numbers and their frequencies without any trouble at all. From there I can put it in a spreadsheet etc.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sun Nov 06, 2005 11:33 pm    Post subject: Are you crazy, man? Pasting a line into what? A DOS-box? My secretary fainted away! Anyway, my wysiwig-solution is defining 2 names, 1 cell-Style, 1 conditional style, 1 formula and 1 filter once and reuse it whenever you have to analyse or print your data. The stylish things are completely optional.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum