Articles

Solved nested if statement problem, if blank then 0 or blank

November 18, 2012 at 12:00:31
Specs: Windows XP

I am working with a nested statement - if n2=y, then apply this and if n2=n apply this, but there will be some cells in the n column that will be blank and I can't seem to figure out how to get them to return either a blank or a zero. Any help is appreciated.

See More: nested if statement problem, if blank then 0 or blank

Report •


#1
November 18, 2012 at 13:43:09

Try these:

=IF(N2="y",1,IF(N2="n",2,0)) for a Zero
=IF(N2="y",1,IF(N2="n",2,"")) for a Blank

MIKE

http://www.skeptic.com/


Report •

#2
November 18, 2012 at 16:01:39

Looked promising for a moment, however I couldn't get it to work

Here is what I am working on. I don't have a lot of skill in excel, just cobbled together so it might not be the prettiest string:

=IF(E2="y",LOOKUP(D2,ALyesRate)*(G2/1000),IF(E2="n",LOOKUP(D2,ALnonRate)*(G2/1000)))
So I need it to return a 0 or blank if E2 is blank


Report •

#3
November 18, 2012 at 18:33:01
✔ Best Answer

Think about the arguments that an IF function uses:

IF(logical_test, [value_if_true], [value_if_false])

Now let's look at your formula and break it down...

=IF(E2="y",LOOKUP(D2,ALyesRate)*(G2/1000),IF(E2="n",LOOKUP(D2,ALnonRate)*(G2/1000)))

The logical_test for your first IF is: E2="y"
The value_if_true is:LOOKUP(D2,ALyesRate)*(G2/1000)
The value_if_false is: IF(E2="n",LOOKUP(D2,ALnonRate)*(G2/1000))

The logical_test for your second IF is: E2="n"
The value_if_true is:LOOKUP(D2,ALnonRate)*(G2/1000)
The value_if_false is: oh, wait, there is no value_if_false for your second IF.

Since you want a blank if E2 doesn't equal "y" or "n", add a value_if_false for your second IF. "" is the equivalent of "nothing" and will produce a blank cell.

=IF(E2="y",LOOKUP(D2,ALyesRate)*(G2/1000),IF(E2="n",LOOKUP(D2,ALnonRate)*(G2/1000),""))

Note: This formula will return an empty cell for any value other than "y" or "n" in E2.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 19, 2012 at 07:24:19

This worked like a champ. Better yet, you walked me through the steps so I learned how to do it myself the next time and how to read the formula. Thanks so much for the lesson.

Report •


Ask Question