IF Formula

[Paula]

First, Im not a programmer or analyst or have any Excel training. Im setting up a spreadsheet to record results of quality reviews on answers to questions. I was proud that I figured out a formula for "Y" = 5, NA =5. Then I added a comma so "N" would =0. But, I realise that leaving an answer blank also generates a "0". I don't want blanks to generate anything. How can I write the IF formula so "Y"=5, NA=5, "N"=0, blanks don't count as anything? Having blank count as zero is causing the averages to be incorrect.

[Logit]

.
Presuming your data is in A1, paste this in B1 :

=IF(A1="","",(IF(A1="Y","5",IF(A1="NA","5",IF(A1="N","0")))))

The formula does not check for anything other than Y, 5 & 0. Anything else will
create a FALSE statement in B1. If A1 is blank, B1 will be blank as well.

[Pecoflyer]

Using double quotes returns text strings instead of numbers
=IF(A1="","",IF(OR(A1="y",A1="NA"),5,0))
To exclude the "" strings from the average use the AVERAGEIF function excluding null strings <>""""