Singletrack Mountain Bike Magazine

• Overview
• Bike Forum
• Chat Forum
• For Sale
• Wanted
• Forum Help
• Classified Rules

Chat Forum

1. stuartie_c - Member

   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

   Posted 7 months ago #
2. CharlieMungus - Member

   You can use countif with continuous values and greater / than less than arguments

   Posted 7 months ago #
3. acjim - Member

   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

   Posted 7 months ago #
4. tinribz - Member

   =COUNTIF(A1:A10,">=0")-COUNTIF(A1:A10,">=1")
   =COUNTIF(A1:A10,">=1")-COUNTIF(A1:A10,">=2")
   =COUNTIF(A1:A10,">=2")-COUNTIF(A1:A10,">=3")

   Posted 7 months ago #
5. stuartie_c - Member

   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

   Posted 7 months ago #
6. mrmo - Member

   are you trying to create a histogram? if so just use the Frequency Array formula.

   excel help

   Posted 7 months ago #
7. stuartie_c - Member

   That could be just what I'm looking for mrmo (and acjim!)

   Ta.

   Posted 7 months ago #
8. Stoner - Member

   have you looked at the histogram function under data analysis in the tools menu

   Posted 7 months ago #
9. stuartie_c - Member

   Not looked there Stoner - where is the equivalent function in Excel 2007?

   Posted 7 months ago #
10. Stoner - Member

    no-eyed-deer.
    F1 it.

    Posted 7 months ago #
11. stuartie_c - Member

    Ah - I need to install it. It has been "configuring" for 5 minutes now and still 0% progress...

    I'll get there.

    Posted 7 months ago #
12. stuartie_c - Member

    Predictably, it ain't there. Need to get the installation disk from work.

    Posted 7 months ago #
13. djaustin - Member

    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

    Posted 7 months ago #
14. Pyro - Member

    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.

    Posted 7 months ago #

RSS feed for this topic

Reply

You must log in to post.