assign a number value to a letter

J

JE McGimpsey

One way:

If the range includes only Y, N or blank:

=COUNTIF(A1:A100,"Y")/COUNTA(A1:A100)

If it can contain more than just Y or N:

=COUNTIF(A1:A100,"Y")/SUM(COUNTIF(A1:A100,{"Y","N"}))
 
R

Rick Rothstein \(MVP - VB\)

I need to assign 1 to Y and 0 to N, so I can run a
average on that column.
Click to expand...

This array-entered formula will do that...

=AVERAGE(IF(H1:H14={"Y","N"},--(H1:H14="Y"),""))

Commit this formula by pressing Ctrl+Shift+Enter instead of just Enter.

Rick
 
R

Rick Rothstein \(MVP - VB\)

I need to assign 1 to Y and 0 to N, so I can run a
This array-entered formula will do that...

=AVERAGE(IF(H1:H14={"Y","N"},--(H1:H14="Y"),""))

Commit this formula by pressing Ctrl+Shift+Enter instead of just Enter.
Click to expand...

After reading JE's posting, I just want to point out that the same formula
(above) will work for both of the conditions he outlined.

Rick
 
D

daddyosworld

I need to assign 1 to Y and 0 to N, so I can run a average on that
column
Click to expand...

Thanks Gentlemen, It did not assign numbers to letters, But it sure
Worked for me.. actually used the second half of JE formula to give
me another equation, worked great.. Thanks once again, you guys are
great.
 
Top