Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-20-2017, 12:34 PM
Paula Paula is offline IF Formula Windows 8 IF Formula Office 2016
Novice
IF Formula
 
Join Date: Sep 2017
Posts: 1
Paula is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 09-20-2017, 03:22 PM
Logit Logit is offline IF Formula Windows 10 IF Formula Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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.
Reply With Quote
  #3  
Old 09-21-2017, 12:39 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF Formula Windows 7 64bit IF Formula Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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 <>""""
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF Formula Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula innkeeper9 Excel 2 09-13-2016 08:59 PM
Need help with dragging a formula and changing a reference column as I drag the formula. LupeB Excel 1 10-22-2015 03:02 PM
IF Formula Need Help w/Formula OSCAR MARTINEZ Excel 1 09-28-2013 05:39 AM
IF Formula formula help excelledsoftware Excel 8 03-25-2012 08:49 AM
IF Formula Help with formula please. AndrewSE Excel 3 04-05-2011 08:50 PM

Other Forums: Access Forums

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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft