assign a number value to a letter

[daddyosworld]

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

 

[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"}))

 

[Rick Rothstein \(MVP - VB\)]

I need to assign 1 to Y and 0 to N, so I can run a

average on that column.

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

 

[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.

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

 

[daddyosworld]

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

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.