#1




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

#2




.
Presuming your data is in A1, paste this in B1 : Code:
=IF(A1="","",(IF(A1="Y","5",IF(A1="NA","5",IF(A1="N","0"))))) create a FALSE statement in B1. If A1 is blank, B1 will be blank as well. 
#3




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 <>""""
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula  innkeeper9  Excel  2  09132016 08:59 PM 
Need help with dragging a formula and changing a reference column as I drag the formula.  LupeB  Excel  1  10222015 03:02 PM 
Need Help w/Formula  OSCAR MARTINEZ  Excel  1  09282013 05:39 AM 
formula help  excelledsoftware  Excel  8  03252012 08:49 AM 
Help with formula please.  AndrewSE  Excel  3  04052011 08:50 PM 