#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 <>"""" 
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 