Summing the number of times a value occurred, into a percentage

[Power55]

Hi,


I've got some data which is assigned to a raw value, eg:


Strongly Disagree = 1
Disagree = 2
Neither Agree nor Disagree = 3
Agree = 4
Strongly Agree = 5


Lets say my raw data is as follows:


Data point #1 : 2
Data point #2 : 3
Data point #3 : 2
Data point #4 : 1
Data point #5 : 5


For clarification, this means that my data is showing that 'Strongly Disagree', 'Neither Agree nor Disagree', and 'Strongly Agree' were selected once, and 'Disagree' was selected twice.


At the moment to build my bar chart I am having to manually sum the number of times a particular data point occurred, and then manually input this into a separate column. This column would then be used in my bar chart.


Eg I would manually count that '2' was produced twice, therefore =2/5. That would then be a data value in my chart.


How can I use a formula to automatically view the number of times each data point was produced and then sum those up (in this example: NOT 2 + 2 because '2' occurred twice, but 2 occurred twice SO THEREFORE the sum of the values occurring would be '2', the sum of the other values only ever occurred once each).


Any way I can do this? I'm not that great at excel.


Thanks

 

[Aladin Akyurek]

Let A2:A10 house the data ("data points").

In B2 enter and copy down for 5 rows:

=COUNTIFS($A$2:$A$10,ROWS($B$2:B2))/COUNT($A$2:$A$10)

 

[Power55]

Let A2:A10 house the data ("data points").

In B2 enter and copy down for 5 rows:

=COUNTIFS($A$2:$A$10,ROWS($B$2:B2))/COUNT($A$2:$A$10)

Thanks!

 

[Aladin Akyurek]

Thanks!

You are welcome.