text formulas
 

I have "Y,N,&NA" in one column. In another column off to the right I am
counting those inputs. A total is then calculating those Y(which formulate
to be 100%) and N (which formulate to be 0%) and then averaging them.

However, I don't want the NA to be part of the average. Meaning, if NA is
input, then the count would be NA, instead of 0% (like the N). It throws off
the average and I want it excluded from the avg calculation

Any thoughts on how to get this to work??

On Mon, 18 Jul 2005 07:06:07 -0700, Rus
wrote:
I have "Y,N,&NA" in one column. In another column off to the right I am
counting those inputs. A total is then calculating those Y(which formulate
to be 100%) and N (which formulate to be 0%) and then averaging them.

However, I don't want the NA to be part of the average. Meaning, if NA is
input, then the count would be NA, instead of 0% (like the N). It throws off
the average and I want it excluded from the avg calculation

Any thoughts on how to get this to work??

For the percentage of Y:

=COUNTIF(A1:A100,"=Y")/COUNTIF(A1:A100,"<NA")

For the percentage of N:

=COUNTIF(A1:A100,"=N")/COUNTIF(A1:A100,"<NA")

MP-
--
"Learning is a behavior that results from consequences."
B.F. Skinner

That one seems to work. However, it gives me the"#DIV/0" and I need them to
read "NA" in that cell for my average calculation to work. Any more
thoughts??

"Mangus Pyke" wrote:

On Mon, 18 Jul 2005 07:06:07 -0700, Rus
wrote:
I have "Y,N,&NA" in one column. In another column off to the right I am
counting those inputs. A total is then calculating those Y(which formulate
to be 100%) and N (which formulate to be 0%) and then averaging them.

However, I don't want the NA to be part of the average. Meaning, if NA is
input, then the count would be NA, instead of 0% (like the N). It throws off
the average and I want it excluded from the avg calculation

Any thoughts on how to get this to work??

For the percentage of Y:

=COUNTIF(A1:A100,"=Y")/COUNTIF(A1:A100,"<NA")

For the percentage of N:

=COUNTIF(A1:A100,"=N")/COUNTIF(A1:A100,"<NA")

MP-
--
"Learning is a behavior that results from consequences."
B.F. Skinner

Try:

=COUNTIF(A1:A100,"Y")/(COUNTIF(A1:A100,"Y")+COUNTIF(A1:A100,"N"))

Tim C

"Rus" wrote in message
...
I have "Y,N,&NA" in one column. In another column off to the right I am
counting those inputs. A total is then calculating those Y(which
formulate
to be 100%) and N (which formulate to be 0%) and then averaging them.

However, I don't want the NA to be part of the average. Meaning, if NA is
input, then the count would be NA, instead of 0% (like the N). It throws
off
the average and I want it excluded from the avg calculation

Any thoughts on how to get this to work??

Or shorter:

=1/(1+COUNTIF(A1:A100,"N")/COUNTIF(A1:A100,"Y"))

Tim C

"Tim C" wrote:

Try:

=COUNTIF(A1:A100,"Y")/(COUNTIF(A1:A100,"Y")+COUNTIF(A1:A100,"N"))

Tim C

"Rus" wrote:

I have "Y,N,&NA" in one column. In another column off to the right I am
counting those inputs. A total is then calculating those Y(which
formulate
to be 100%) and N (which formulate to be 0%) and then averaging them.

However, I don't want the NA to be part of the average. Meaning, if NA
is
input, then the count would be NA, instead of 0% (like the N). It throws
off
the average and I want it excluded from the avg calculation

Any thoughts on how to get this to work??