Count Text to get a Percentage
 

Hi,

I have 12 offices and each needs to do a reporting exercise.

Part of that exercise is to enter a Yes or No into a column - example would
be: 'Have you carried out a H&S Check?' - Validation List = Yes or No

From that answer I need to get a % of the 12 that answer Yes and a % of
those that answer No - the results will be displayed in seperate colunms on
a Master Tracker Sheet I have - hope that makes sense

Is there a function for this?

regards

Craig

Answer: Count Text to get a Percentage
 

Hi Craig,

Yes, there is a function in Excel that can help you with this. You can use the COUNTIF function to count the number of "Yes" and "No" responses and then divide by the total number of offices to get the percentage.

Here are the steps to do this:

1. Open your Master Tracker Sheet and create two columns for the percentages - one for "Yes" and one for "No".
2. In the "Yes" column, enter the following formula:
   Formula:

   ---

   =COUNTIF(range,"Yes")/12*100 


   ---

   Replace "range" with the range of cells where the "Yes" responses are entered. The formula divides the count of "Yes" responses by 12 (the total number of offices) and then multiplies by 100 to get the percentage.
3. In the "No" column, enter the following formula:
   Formula:

   ---

   =COUNTIF(range,"No")/12*100 


   ---

   Replace "range" with the range of cells where the "No" responses are entered. The formula divides the count of "No" responses by 12 (the total number of offices) and then multiplies by 100 to get the percentage.
4. The percentages should now be displayed in the respective columns.

Count Text to get a Percentage
 

With the cell formatted as percentage, something like this...
=COUNTIF(B1:B12,"Yes")/12

--
Best Regards,

Luke M
"Craig" wrote in message
...
Hi,

I have 12 offices and each needs to do a reporting exercise.

Part of that exercise is to enter a Yes or No into a column - example
would
be: 'Have you carried out a H&S Check?' - Validation List = Yes or No

From that answer I need to get a % of the 12 that answer Yes and a % of
those that answer No - the results will be displayed in seperate colunms
on
a Master Tracker Sheet I have - hope that makes sense

Is there a function for this?

regards

Craig

Count Text to get a Percentage
 

Assuming that your Yes/No responses are in F2:F13, then:
=COUNTIF(F2:F13,"yes")/12
and for the no percentage, either
=COUNTIF(F2:F13,"no")/12
or, assuming the yes percentage is in G2:
=1-G2
Those will give you a decimal number, format as percentage to see them
expressed as a percentage.


"Craig" wrote:

Hi,

I have 12 offices and each needs to do a reporting exercise.

Part of that exercise is to enter a Yes or No into a column - example would
be: 'Have you carried out a H&S Check?' - Validation List = Yes or No

From that answer I need to get a % of the 12 that answer Yes and a % of
those that answer No - the results will be displayed in seperate colunms on
a Master Tracker Sheet I have - hope that makes sense

Is there a function for this?

regards

Craig

Count Text to get a Percentage
 

Thank you Luke M and JLatham......Both methods work

My boss has more functions for me to sort - some of which I will need help
on - your speedy and accuate response and assistance makes this soo much
easier

Excellent, Thanks

Craig

"Luke M" wrote:

With the cell formatted as percentage, something like this...
=COUNTIF(B1:B12,"Yes")/12

--
Best Regards,

Luke M
"Craig" wrote in message
...
Hi,

I have 12 offices and each needs to do a reporting exercise.

Part of that exercise is to enter a Yes or No into a column - example
would
be: 'Have you carried out a H&S Check?' - Validation List = Yes or No

From that answer I need to get a % of the 12 that answer Yes and a % of
those that answer No - the results will be displayed in seperate colunms
on
a Master Tracker Sheet I have - hope that makes sense

Is there a function for this?

regards

Craig


.

Count Text to get a Percentage
 

Apologies, they arent 'both methods' but I've got it thanks....

"Craig" wrote:

Thank you Luke M and JLatham......Both methods work

My boss has more functions for me to sort - some of which I will need help
on - your speedy and accuate response and assistance makes this soo much
easier

Excellent, Thanks

Craig

"Luke M" wrote:

With the cell formatted as percentage, something like this...
=COUNTIF(B1:B12,"Yes")/12

--
Best Regards,

Luke M
"Craig" wrote in message
...
Hi,

I have 12 offices and each needs to do a reporting exercise.

Part of that exercise is to enter a Yes or No into a column - example
would
be: 'Have you carried out a H&S Check?' - Validation List = Yes or No

From that answer I need to get a % of the 12 that answer Yes and a % of
those that answer No - the results will be displayed in seperate colunms
on
a Master Tracker Sheet I have - hope that makes sense

Is there a function for this?

regards

Craig


.