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
Is this what you are trying to do?:
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
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
OK. My bad I think I got it.
For the table Frequency
B2=COUNTIF(Data :: A, "="&A2)
select B2 and fill down
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
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
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
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.
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
Counting values and creating a chart
