Possible Easy Percent Question

cme14041

Hi!

I am trying to do a percentage of how many yes and how many nos I have in one column.  I tried a couple of formulas and I am coming with some weird answers.  For instance.

=[Percentage of IPE Yes and No]1 - [Percentage of IPE Yes and No]2 / [Percentage of IPE Yes and No]1 and my answers comes out to be 4,773%

I have also tried the =CountIf (##:##. "Yes") /  Count(##:##)

I have already have a formula for the total number for "Yes" and a formula for the total number of "No" which is an example of the following

=COUNTIF(IPCE1:IPCE520, "No")

Which I used a different column for the totals of Yes and No,  Now I am trying to use the same Column but different row to calculate the percentage of both?  

Hopefully, this makes sense.  

Can someone please help? 

I really think I am over thinking it.

Mike Wilday

I don't have time to write this for you but wouldn't you want to calculate the total number of yeses and nos and then divide the total into the total number of just yeses to calculate the percentage for yeses, then divide the total number into the totall number of just nos to get the percentage for nos? 

Yes percentages would look something like this: 

=Countif(Yes/noColumn, "Yes") \ CountIFS(Yes/noColumn, "Yes", "No")  

You might have to multiply the result by 100 to get your percentage.

J. Craig Williams

cme,

You are on the right track

=COUNTIF(IPCE1:IPCE520, "No") / COUNT(IPCE1:IPCE520) * 100

will give you the percentage of "No" over all rows 1-520 that have some data entered (not necessarily Yes or No). If the column or cell is formatted for %, you can and should drop the * 100 part as 0.333 will be displayed as 33.3% by the formatting.

Don't put this equation in IPCE rows 1 to 520, that will result in a circular reference (ie won't work)

If you'd rather count the number of No's out of all answers Yes or No (but not anything else), then you'd want to use something like Mike provided - but not his because it is malformed*

=COUNTIF(IPCE1:IPCE520, "No") / (COUNTIF(IPCE1:IPCE520, "Yes") + COUNTIF(IPCE1:IPCE520, "No"))

Do the same for "Yes" and Bob's your Uncle.

Hope that helps.

Craig

* The division symbol is / not \ and COUNTIFS will return a count for ALL matches, not ONE OR MORE.

 

 

Mike Wilday

Thanks for that correction Craig! " / not \ "