re: Easy nested Excel &quot;if&quot; statement: Who can solve it?

Posted by finchmeister08

Member since Mar 2011

37916 posts

Posted on 5/5/17 at 9:20 am to Lou Pai

#n/a

The thing is, I have to compare if one cell against 3 different ranges while comparing if another cell equals yes or no.

Posted by DirtyMikeandtheBoys

Member since May 2011

19459 posts

Posted on 5/5/17 at 9:20 am to Lou Pai

#pcloadletter

Posted by Jester

Baton Rouge

Member since Feb 2006

34705 posts

Posted on 5/5/17 at 9:21 am to finchmeister08

<-- Tech Board

Posted by finchmeister08

Member since Mar 2011

37916 posts

Posted on 5/5/17 at 9:23 am to Jester

Move it

Posted by Lou Pai

Member since Dec 2014

29215 posts

Posted on 5/5/17 at 9:24 am to finchmeister08

Really hard to think about this without looking at the file, but have you tried doing a sumif array at all? Not sure if it would help.

Posted by pjab

Member since Mar 2016

5727 posts

Posted on 5/5/17 at 9:25 am to finchmeister08

That shite looks like a mess. Do one piece at a time until you get what you want.

Posted by LSUtigerME

Walker, LA

Member since Oct 2012

3892 posts

Posted on 5/5/17 at 9:26 am to finchmeister08

Use Evaluate formula and step into each nested statement to find and isolate the error.

Posted by pjab

Member since Mar 2016

5727 posts

Posted on 5/5/17 at 9:27 am to Lou Pai

Good advice. Try something else. Limit the ifs.

Posted by chillygentilly

70122

Member since Aug 2012

2614 posts

Posted on 5/5/17 at 9:44 am to finchmeister08

On your first two, your last IF statement doesn't have a value_if_false. You need one more argument.

Posted by FeauxPaw

BRuh

Member since Sep 2015

1000 posts

Posted on 5/5/17 at 9:48 am to finchmeister08

=IFIFIFIFIFIFIFIF("Okie", "Dokey")

Posted by gthog61

Irving, TX

Member since Nov 2009

71001 posts

Posted on 5/5/17 at 9:49 am to finchmeister08

Instead of trying to nest all that, do each instance separately, with the NO option at the end equal to "".

Then concatenate them all together.

Posted by Jim Rockford

Member since May 2011

102233 posts

Posted on 5/5/17 at 9:51 am to tke857

Your answer is here

Posted by Chucktown_Badger

The banks of the Ashley River

Member since May 2013

33869 posts

Posted on 5/5/17 at 9:52 am to gthog61

I thought I was an excel whiz because I could insert a drop down menu into a cell. Clearly I need to never be satisfied.

Posted by finchmeister08

Member since Mar 2011

37916 posts

Posted on 5/5/17 at 10:06 am to Chucktown_Badger

i got it!

=IF(AND(SUMPRODUCT(--(Panels_1_1_4=Windows!C33)), Windows!C34="No"), CONCATENATE(Windows!C33, " Jamb Fin"), IF(AND(SUMPRODUCT(--(Panels_1_1_4=Windows!C33)), Windows!C34="Yes"), "Universal Jamb Fin", IF(SUMPRODUCT(--(IWP_Panels=Windows!C33)), "InsulVIEW", IF(SUMPRODUCT(--(Other_Panels=Windows!C33)), CONCATENATE(Windows!C33, " Jamb Fin", "" )))))

THAT'S THE ANSWER!!!

Posted by someLSUdoosh

Baton Rouge

Member since Oct 2016

882 posts

Posted on 5/5/17 at 10:30 am to pjab

quote:
That shite looks like a mess. Do one piece at a time until you get what you want.

Break it out and make each section work on its own. And then put it together. That will make it easier to spot an issue rather than just looking at the entire formula at one time.

Posted by mikelbr

Baton Rouge

Member since Apr 2008

48581 posts

Posted on 5/5/17 at 10:37 am to finchmeister08

WHY IN THE frick DON'T YOU HAVE VBA FIRE OFF ON AN EVENT OR PUT A frickING BUTTON ON THE WORKSHEET?

You must be something besides IT with this bullshite.