Microsoft Office Forums IF Formula
 Register FAQ Search Today's Posts Mark Forums Read

#1
09-20-2017, 12:34 PM
 Paula Windows 8 Office 2016 Novice Join Date: Sep 2017 Posts: 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
09-20-2017, 03:22 PM
 Logit Windows 10 Office 2007 Competent Performer Join Date: Jan 2017 Posts: 216

.
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")))))`
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.
#3
09-21-2017, 12:39 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,150

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post innkeeper9 Excel 2 09-13-2016 08:59 PM LupeB Excel 1 10-22-2015 03:02 PM OSCAR MARTINEZ Excel 1 09-28-2013 05:39 AM excelledsoftware Excel 8 03-25-2012 08:49 AM AndrewSE Excel 3 04-05-2011 08:50 PM

All times are GMT -7. The time now is 03:14 AM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top