| |
| | |

Apple Phoon Forum

 Forgot password?
 Register
View: 692|Reply: 2

Totalling text entries

[Copy link]

Post time: 2014-02-18 19:28:28 |Show all posts
I've put together a spreadsheet for responses to a questionnaire I've collated. Which formula do I use to show the percentage in one cell which depicts a Yes response from people listed on the questionnaire. I have 70 people on the spreadsheet and if someone has responded to a question saying Yes, I want it to be reflected as a percentage of the 70 people, so if 35 people say yes, I want the percentage cell to say 50%.
Can anyone help please?
Thanks,
Simon.
        iPod touch,  iOS 5.1.1
Reply

Use magic Report

Post time: 2014-02-18 22:03:42 |Show all posts
Hi Simon,
Here's a variation on Wayne's suggestion that uses fewer formulas:



Percent is the fraction of the whole sample. The first formula, in E2, calculates the fraction (the same as shown in E6 of Wayne's table. Choosing Percent as the format displays the fraction as a percent.
E2: =COUNTIF(B,"Y")/COUNTA(B)
Since each respondent must choose one of two answers, the fraction answering "No" must make up the rest of the whole sample. The second formula, in E3, subtracts the "YES" value, in E2, from the whole sample ( 1 ), to obtain the fraction answering "No". Formatting displays that as a percent.
E3: =1-E2
Regards,
Barry
Reply

Use magic Report

Post time: 2014-02-18 20:58:08 |Show all posts
you can use countif() to count some range based on a condition and counta() to count how many total questions there are. The percentage is the ratio of the the two... that is



E2=COUNTIF(B, "=YES")
E3=COUNTIF(B, "=NO")
E4=COUNTA(A)
E6=E2/E4
E7=E3/E4
select E6 and E7, then format as percentage using the cell inspector
Reply

Use magic Report

You have to log in before you can reply Login | Register

Archive| Apple Phoon

2019-12-13 13:18 GMT-8 , Processed in 0.074137 sec., 27 queries .

Powered by Discuz! 7.2

Release 20121101, © 2001-2019

To Top