Non-numeric percentage excluding blank results

[clintonmadden]

Hi, I want to create a formula to calcualate to calculate the percentage of yes's compared to no's in a table. The closest thing i could find was

=countif(a1:a10,"yes")/counta(a1:a10)

or

=countif(a1:a10,"yes")/10

but there could be a different number of results recorded and I only want a percentage of yes's compared to the total number of entries.

Thanks for any help you can give me.

 

[vbaInet]

Maybe something like this could work?

=(countif(a1:a10,"yes")/countif(a1:a10,"no")) * 100

Welcome to AWF by the way

 

[clintonmadden]

Hi, thanks very much for the reply. I realise I had worded my question wrong. I was trying to get a percentage of yes's comapred to the total number of respones in a selection area. So I guess I need to find the value of yes's divided by yes's and no's,

I tried =(countif(a1:a10,"yes")/countif(a1:a10,"yes,no")) * 100

but it didn't like that, is there a different format I should be using to ask it to do this?

Thanks

 

[vbaInet]

So there will be blanks as well and you don't want that calculated right?

 

[clintonmadden]

Yes, the blank spaces can be ignored, I know I've seen something to do that before but I can't remember or find it.

 

[vbaInet]

It would be a matter of adding another COUNTIF Try this:

=(countif(a1:a10,"yes")/(countif(a1:a10,"yes") + countif(a1:a10,"no"))) * 100

 

[clintonmadden]

Wow, that's great!

Thanks alot mate you're a star!

I appreaciate your help

 

[vbaInet]

I think you could also use the AND operator instead of two COUNTIFs to the right. Investigate how it works.

No worries mate. You're welcome.

 

[clintonmadden]

Thanks, I'll have a look into it. Good to know how it works now!

 

[vbaInet]

Goodie!

 

[clintonmadden]

I have put a data validation list in the cells so that the user has to choose either yes or no, however, it seems the formula isn't taking these results into account. It works perfectly when I type the words in manually, is there a way I can get the formula to register these results?

Thanks again

 

[vbaInet]

Not sure what's happening there. It seems to work for me. Maybe you need to match the case. Change the column type to GENERAL or TEXT.

 

[clintonmadden]

I tried it on general and text but get the same result. Still has the #DIV/0! in the box. I noticed though if I choose all "no's" it correctly gives me 0%

 

[clintonmadden]

Actually, ignore me I'm being an idiot today! lol
I realised it is working correctly, but the result box was too small to display #DIV/0! so just showed #### so when I started testing the results it showed #### still. The problem was I had formatted the cells with the formula in to display percentages and there is also a *100 in the formula so it was reporting a result a 2500% instead of 25%. It's all sorted now, thanks again for all your help.

 

[vbaInet]

As you may know any number divided by 0 will produce an error. Try it out in a calculator You have to manage that too and replace the result of the denominator if it equates to 0 with 1.

=(COUNTIF(A1:A10,"yes")/IF((COUNTIF(A1:A10,"yes")+COUNTIF(A1:A10,"no"))=0,1,(COUNTIF(A1:A10,"yes")+COUNTIF(A1:A10,"no")))*100)

 

[Brianwarnock]

Counta ignores, ie does not count, empty cells, perhaps you have more values than yes no and empty else your original formula should have worked.

Brian

 

[clintonmadden]

The original formula did work, I made a post just before vbaInet posted the second formula to mention the mistake I'd made. All working well now, thanks

 

[Brianwarnock]

Cant say I saw a post saying the original formula worked, but still glad you are sorted.

Brian

 

[vbaInet]

Excellent! Brian does like his formulas though