1
I have an excel sheet that looks like this:
I want a function that will tell me the percentage of each response. So 36% blue, 27% red, etc. I found this article that shows how to do this kind of thing, but only with a yes/no answer. I want to be able to calculate it, so that if I add another row, and the next person's favourite colour is purple, it will include that in the response.
Any suggestions?
eshellborn
Posted 2016-08-22T20:54:27.583
Reputation: 115
2
=COUNTIF($B$2:$B$12,E2)/COUNTA($B$2:$B$12)
Countif to count colours
CountA to count answers
Countif / CountA to have the percentage of each colour
$B$2:$B$12 the range of colours
E2 the unique value of each colour
1
In Column C list the unique values (can also get them from Advanced Filtering). Then in D1 =COUNTIF($B:$B,"="&C1)/(COUNTA($B:$B)-1) and fill that down.
atclaus
Posted 2016-08-22T20:54:27.583
Reputation: 146
forgot the divide by total at first – atclaus – 2016-08-22T21:25:50.423
COUNTA($B:$B) will include the title row in the count. You will want to subtract 1 from the denominator to make up for it. (COUNTA($B:$B) - 1) – Scott Craner – 2016-08-22T21:56:43.070
Thanks @ScottCraner didn't catch there was a title row. Fixed my post. – atclaus – 2016-08-22T22:00:09.170
Thanks! Now, is it possible to automatically generate the list of colours? – eshellborn – 2016-08-23T21:08:36.870
You can copy all the colours paste it in new column and use remove duplicates (Data Tab) – None – 2016-08-23T21:50:29.853