7 Replies Latest reply: May 25, 2012 11:44 AM by Barry
Peter Gibb Level 1 Level 1 (5 points)

I would like to create a chart showing the percentages of each range of values in a particular column in the spreadsheet.  There are 125 rows of records and one of the columns has 7 different text values.  I would like to chart the different values in this column.  Hope that is clear; thanks in advance

  • 1. Re: Counting values and creating a chart
    Wayne Contello Level 6 Level 6 (13,620 points)

    Is this what you are trying to do?:

     

    Screen Shot 2012-05-24 at 9.29.21 AM.png

     

    The data table contains randomw data which the table "Frequency" summarizes in bins I made up.  I color coded the bins to simplify checking.

     

    the formula in the table "Frequency" is:

    B2=INDEX(FREQUENCY(Data :: A, $A$2:$A$8), ROW()-1)

     

    select B2 and fill down

  • 2. Re: Counting values and creating a chart
    Peter Gibb Level 1 Level 1 (5 points)

    Thanks Wayne.  It may be but I don't think so.  I have a list and i want to count the number of times each value occurs and also display it as a chart, probably showing it as percentages.  I know it starts as a Countif function.  I was hoping that there is a shorter way than just counting them as one action and then displaying a chart as a separate action.

     

    I also want to produce the countif data in a different table.  Thanks

  • 3. Re: Counting values and creating a chart
    Wayne Contello Level 6 Level 6 (13,620 points)

    OK.  My bad I think I got it.

    Screen Shot 2012-05-24 at 12.11.27 PM.png

    For the table Frequency

    B2=COUNTIF(Data :: A, "="&A2)

    select B2 and fill down

  • 4. Re: Counting values and creating a chart
    Barry Level 7 Level 7 (29,180 points)

    HI Peter,

     

    I'm confused here: "I was hoping that there is a shorter way than just counting them as one action and then displaying a chart as a separate action."

     

    Numbers will chart the data you give it. If you want it to chart "the percentages of each range of values in a particular column in the spreadsheet," then you have to provide it with either "the percentage of each range of values in that particular column in the spreadsheet" or "the count of each range of values in that particular column in the spreadsheet."

     

    The second (provide the count) is what Wayne has done in his second post above. He has also instructed Numbers, using the Chart Inspector, to label the individual wedges with the percentage of the total count, rather than the count itself.

     

    Regards,

    Barry

  • 5. Re: Counting values and creating a chart
    Peter Gibb Level 1 Level 1 (5 points)

    Hi Barry,

     

    I wasn't as clear as I thought I was.  I am pretty sure that Wayne has solved the problem for me; Thanks very much Wayne.

     

    Thanks to both.

     

    Peter

  • 6. Re: Counting values and creating a chart
    Peter Gibb Level 1 Level 1 (5 points)

    Hi Wayne, Barry

     

    I have two more queries please.

     

    Some of the values I want to count are text values - no problem; but is there a way of counting a text value that contains particular text in different forms.  Example is that some cells contain the text "Master" and some "Snr Master".   I want all variations of "Master" to be counted.  Can I count all cells that contain either of these?  e.g If there are 46 'Master" and 25 "Snr Master", can I count so that numbers adds up to 71?

     

    Finally, I hope.  Is there a way of counting all of the 'other' values as one?  I have a lot of single values and pairs of values which I would like to keep in the table, but for charting purposes need to call "Others" or "Not included" in the main sets.

     

    Thanks very much again

     

    Peter

  • 7. Re: Counting values and creating a chart
    Barry Level 7 Level 7 (29,180 points)

    Hi Peter,

     

    Here's a quick example that counts the "Master" variations and the "other" entries.

    Text counted is in column B. "masters" count in C2,other specific counts would be in C3, C4, C5 and C6. "Other" count in C8 calculates the number not included in C2-C6.

    Picture 5.png

     

    C2: =COUNTIF(B,"=*aster")

     

    C8: =COUNTA(B)-SUM(C2:C6)

     

    Some care in setting the conditions and in naming the categories will be needed to avoid items being counted in more than one category.

     

    Regards,

    Barry