Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
Seems to be a lot of Excel expertise in here...
I want to make a line chart to show the distribution of a range of values. Say I have 100 (or 1000) values in a column, all in the range 1 to 7 (ideally I'd like it to deal with continuous values but I'll settle for discrete for now). I want a chart showing the distribution from 7 to 1 (ie lowest to highest - 7 is the "lowest" value in the range because they represent grades (discrete) or predictors (continuous, with 2 d.p.)). I think discrete values are quite easy because I can probably use the COUNTIF function
I suspect discrete values are tricky but I'd love to discover a simple method. If I can do it in Excel, I can try and tackle it in Filemaker Pro 11 Advanced.
Thanks!
SC
You can use countif with continuous values and greater / than less than arguments
Don't have Excel on this notebook but i think what you want is a pivot table / chart or possibly a frequency histogram (via the analysis toolpack) if you want to stay away from pivot tables
=COUNTIF(A1:A10,">=0")-COUNTIF(A1:A10,">=1")
=COUNTIF(A1:A10,">=1")-COUNTIF(A1:A10,">=2")
=COUNTIF(A1:A10,">=2")-COUNTIF(A1:A10,">=3")
Cheers all.
I think charting distribution of values between 1 and 7 with 2 d.p. is asking a bit much since it's effectively 600 (?) discrete values. ROUND and COUNTIF will suffice for now. Just got to work out how to do it in Filemaker...
SC
are you trying to create a histogram? if so just use the Frequency Array formula.
[url= http://support.microsoft.com/kb/100122 ]excel help[/url]
That could be just what I'm looking for mrmo (and acjim!)
Ta.
have you looked at the histogram function under data analysis in the tools menu
Not looked there Stoner - where is the equivalent function in Excel 2007?
no-eyed-deer.
F1 it.
Ah - I need to install it. It has been "configuring" for 5 minutes now and still 0% progress...
I'll get there.
Predictably, it ain't there. Need to get the installation disk from work.
You want the Frequency array formula as stated above:
create a column 1...7 in say A1 to A7
select the cells to the right of this B1 to B7
type =FREQUENCY(A1:A7, data range)
here's the tricky part type CTRL+SHIFT+ENTER to fill in the whole array
plot this as a bar chart.
Note you can alter the bins (1..7) but have to change the whole array when selecting more/different data
In 2007 just use a Pivot Table:
Highlight the column with your grade data in it
Under the 'Insert' menu, hit PivotTable
When it brings up the Field List, drag your Column title to the Data Items field and then to the Values field. Make sure, since you're using numeric values, the Values box then reads 'Count of <column title>' not 'Sum of < >'. If the latter, right click on that item in the Valeues field, click on 'Value Field Settings' in the menu box that appears, and set it to 'Count'.
Bob's your probably-just-an-old-friend-of-the-family, you've got a little table that tells you the frequency of each discrete item, same as if you'd done a long sequence of CountIfs.