[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1041
  • Last Modified:

Frequency Plot under Excel

I need to produce frequency plots for data which has already been analysed. Effectively, I have produced a histogram table and I now want excel to chart it. Is there any easy way of doing this?

I don't want to use a scatter plot, neither do I want to use a bar chart which is labelled with the bin values. Is there any way to get excel to generate a simple bar chart of data-range against frequency?

cheers,

Adam
0
AJFleming
Asked:
AJFleming
  • 5
  • 2
  • 2
1 Solution
 
calacucciaCommented:
Hi Adam,

'm not sure this is exactly what you're looking for, but I would do the following:

Imagine your Histogram table (Bin/Frequency) is created from A1 on left top,

insert a column between column Bin and column Frequency and enter following formula in cell B2:

=IF(A1="Bin";"Under "&A2;IF(A2="More";"More than "&A1;A1& " - "&A2))

This will create the Data Range in column B.

Now select the data of columns B and C and use a column chart, this will result in a Frequency against data range chart with bars.

Hope this helps,

Calacuccia
0
 
ZethCommented:
Hi AJFleming!

Even i is not quite sure what it is you want to do. (i'm not quite sure what you mean by "frequency"?) But this formula count how many appearances you have of the concatenated values in C1 & D4 in the range row by row concatenation of the ranges D1:D100 & G1:G100.
You get the answer as a number in the formula cell. This is usable if don't have to many unique values to count the number of equalvalues from.

Type this formula in the formula bar:
=Count(If(Sheet1!$A$1!:$D$100&Sheet1!$G$1:$G$100=Sheet2!$C$1&D$4;1))
Then press Ctrl+Shift+Enter to quit edit mode. Then you shall have "{}" around the formula.

The ranges shall be replaces to your needs. there can be only one range to the left of the equal sing, and just one cellreference or value to the right of the "="-sign.


If you have many different unique values, for which you want to count the number of equal values, then you shall do an pivottable to build your graph from.
You can get help with that to here if that is what you want?


Hope that is to some help!
Even if you don't come all the way!

Zeth

PS
Maybe the "Count" shall be "Number" or something in your XL if you don't have the count formula.
0
 
calacucciaCommented:
Hi Zeth,

How was the snowfall this year (visited your website) ?

I'd like to explain you wat AJFleming did: he used the Data Analysis Toolpak in which you can find a Histogram function. This add-on function creates a 2-column table, first column is named 'BIN' (containing the data ranges delimiters) and a second column named 'FREQUENCY' which counts how many times the data were in the corresponding data range.
This all results in a gaussian histogram. His question, I believe, is how to plot from there on this table (which he has created) as good and efficient as possible. That's why I proposed him to insert a column, and with a text formula transform the 'BIN' values to the 'Data Range' he requested, (e.g.  the Data Range list would include respectively: Under 1   1 - 2     2 - 3    Above 3)


Cheers,

Calacuccia
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
AJFlemingAuthor Commented:
Thanks for the responses guys.

Caluccia, can you explain exactly what the formula you've given is doing? I'm not actually using the D.A.T. to generate the histogram table, the table's being generated elsewhere so the formatting may be different.

What I'm basically trying to do is generate a histogram given already analysed data. I've got a table of values like this

Value    Count
1             50
2             60
3             70
4             55
5             35
6             12
10            1
11            1
12            1

What I'm looking for is some way of charting the count against the value _without_ having to place the values as labels. I want to generate a bar-chart with a bar for each of the values. When I try and generate a bar-chart all I get is 2 data ranges which bear no resemblance to what I'm looking for.

cheers,

Adam...
0
 
calacucciaCommented:
Hi AJFleming,

First of all, let's try to get you a god looking histogram from the table you printed here:

1) Select the data range
2) Click on the chart wizard
3) Select 'Column Graphs', sub-type Clustered column (left top), Click Next
4) In step 2 of the Wizard, go to the series Tab, where you see on the bottom: 'Category (X) Axis labels, enter the box (with mouse), and select the X-axis labels on the main sheet (your histogram x-axis with the Values), this will result in a formula in that text box like: "=Sheet1!$A$2:$A$10" if your sheet with data is named sheet 1.
5) Click on Series 'Values' on same tab, and click remove to remove this unnecessary data serie.
6) Click Next button, and continue the graph formatting at your demands.

That should do the job.

About the Histogram function from the Data Analysis Toolpka, this function performs a data analysis of a set of values (e.g. a table with a bunch of values between 0 and 1) and outputs a two column table, looking like the one you've put in your last comment, with column headers: bin (corresponding to your values column) and frequency (corresponding to your count column).

This would look like:
Bin      Frequency

0.1           14
0.2           25
0.3           5
0.4           7
0.5           8
0.6           10
0.7           25
0.8           35
0.9           2
More        1

The meaning of this table is, there were counted 14 records under 0.1, 25 values between 0.1 and 0.2,...., 2 records between 0.8 and 0.9 and finally 1 records above 0.9.

The function should be found under Tools/Data Analysis, but if you haven't installed the Toolpak, you have to load it under Tools/Add-Ins, and select/check the Data Analysis Toolpack in the Add-ins list.

If it doesn't appear in that list, you have to run the Office setup, and Add the corresponding files from the Office CD.

Hope I helped you,

Calacuccia
0
 
ZethCommented:
Hi AJFlemming and Calacuccia!
AJFlemming, hope you don't mind this little comment to Calacuccia!
Calacuccia!
Thank's for your information about the dataanalysis toolpak, i will now take a closer look at it, it seems very usefull. And i think that it also will make mee extremly rich, i will now analyse the frequence of the numbers in the swedish "lotto-gamble" and predict which numbers that most likely will come next week 8-) hehehe.
The snowfall was normal this year, but spain and italy seems to get it instead.

Zeth
0
 
calacucciaCommented:
Hi AJFleming,

is the problem solved ?

Calacuccia
0
 
calacucciaCommented:
Grading to an answer .....
0
 
AJFlemingAuthor Commented:
Problem solved - I've found a work-around. Forgot this question was still open - sorry.

Cheers,

Adam...
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now