OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Make a Histogram

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
tomas3
General User
General User


Joined: 05 Nov 2005
Posts: 6

PostPosted: Sat Nov 05, 2005 5:55 pm    Post subject: Make a Histogram Reply with quote

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
Back to top
View user's profile Send private message
RonIA
Super User
Super User


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

PostPosted: Sat Nov 05, 2005 6:45 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Nov 05, 2005 6:56 pm    Post subject: Re: Make a Histogram Reply with quote

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.
Back to top
View user's profile Send private message
tomas3
General User
General User


Joined: 05 Nov 2005
Posts: 6

PostPosted: Sat Nov 05, 2005 9:13 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
RonIA
Super User
Super User


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

PostPosted: Sun Nov 06, 2005 5:07 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Nov 06, 2005 10:50 am    Post subject: Re: Make a Histogram Reply with quote

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;BCool
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(ACool;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.
Back to top
View user's profile Send private message
tomas3
General User
General User


Joined: 05 Nov 2005
Posts: 6

PostPosted: Sun Nov 06, 2005 8:17 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Nov 06, 2005 11:33 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group