Can you help me write this formula?

[blazon]

I'm trying to create a formula that involves counting and percents. So, in the range A9:AE9, I want to count the number of times "y" appears (each cell of the range has a "y", "n", or "n/a") and divide that number by 31 (the number of columns in the range). It sounds simple, but I can't figure it out!

Any help is appreciated.

Thanks!

[swatsp0p]

Try this:

=COUNTIF(A9:AE9,"=y")/31

[blazon]

Hey, that works, thanks!

I just realized that I don't want the 'n/a' to negatively impact the percent. I'm basically tracking goals and so ideally, if everyone meets their goal, they will achieve 100%. If a 'n/a' is recorded, is there a way then that I can not have that cell counted in the calculation of the percent? So, if there are 3 'n/a's, then rather than dividing by 31, excel would divide by 28?

Thanks so much for your help!

[swatsp0p]

Remember, you are dividing by the number of columns. Is there only one entry per column, therefore, an N/A makes that column not part of the group?

[blazon]

Yes, that's correct.

[swatsp0p]

Then subtract the number of "n/a" from the count of columns (31), as such:

=COUNTIF(A9:AE9,"=y")/(31-COUNTIF(A9:AE9,"=n/a"))

Should work.

[Sloth]

=COUNTIF(A9:AE9,"=y")/(COUNTA(A9:AE9)-COUNTIF(A9:AE9,"=n/a"))

or

=COUNTIF(A9:AE9,"=y")/(31-COUNTIF(A9:AE9,"=n/a"))

(If the number of columns is always 31)

These formulas should work.

"blazon" wrote:

>
> Hey, that works, thanks!
>
> I just realized that I don't want the 'n/a' to negatively impact the
> percent. I'm basically tracking goals and so ideally, if everyone meets
> their goal, they will achieve 100%. If a 'n/a' is recorded, is there a
> way then that I can not have that cell counted in the calculation of
> the percent? So, if there are 3 'n/a's, then rather than dividing by
> 31, excel would divide by 28?
>
> Thanks so much for your help!
>
>
> --
> blazon
> ------------------------------------------------------------------------
> blazon's Profile: http://www.excelforum.com/member.php...o&userid=27409
> View this thread: http://www.excelforum.com/showthread...hreadid=469224
>
>