assign a number value to a letter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

daddyosworld@gmail.com - 02 Nov 2007 04:24 GMT

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

Reply to this Message

JE McGimpsey - 02 Nov 2007 05:07 GMT

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

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

Reply to this Message

Rick Rothstein (MVP - VB) - 02 Nov 2007 05:26 GMT

> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 02 Nov 2007 06:03 GMT

>> I need to assign 1 to Y and 0 to N, so I can run a
>> average on that column.
[quoted text clipped - 4 lines]
>
> 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

Reply to this Message

daddyosworld@gmail.com - 02 Nov 2007 07:03 GMT

On Nov 1, 10:24 pm, daddyoswo...@gmail.com wrote:

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

Reply to this Message

Rate this thread: