#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 <>""""
__________________
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 |
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 | 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 |
Need Help w/Formula | OSCAR MARTINEZ | Excel | 1 | 09-28-2013 05:39 AM |
formula help | excelledsoftware | Excel | 8 | 03-25-2012 08:49 AM |
Help with formula please. | AndrewSE | Excel | 3 | 04-05-2011 08:50 PM |