Started By
Message

Easy nested Excel "if" statement: Who can solve it?

Posted on 5/5/17 at 9:05 am
Posted by finchmeister08
Member since Mar 2011
35669 posts
Posted on 5/5/17 at 9:05 am
I can't seem to get the formula to work past the first 2 instances of the "if" statement. i've used 3 different ways to write this formula, and they all fail at the same spot. Here it is...

1) =IF(AND(Windows!C33=Info!N21,Windows!C34="No"),CONCATENATE(Windows!C33," Jamb Fin"),IF(AND(Windows!C33=Info!N21,Windows!C34="Yes"),"Universal Fin",IF(AND(Windows!C33=Info!N22,C34="No"),"InsulVIEW Jamb Fin",IF(AND(Windows!C33=Info!N22,C34="Yes"),"InsulVIEW Jamb Fin",IF(Windows!C33=Info!N23, "Universal Jamb Fin")))))

2) =IF(C34="No", IF(C33=Info!N21, CONCATENATE(C33, " Jamb Fin"), IF(C33=Info!N22, "InsulVIEW Jamb Fin", IF(C33=Info!N23, "Universal Jamb Fin"))),IF(C34="Yes", IF(C33=Info!N21, "Universal Jamb Fin", IF(C33=Info!N22, "InsulVIEW Jamb Fin", IF(C33=Info!N23, "Universal Jamb Fin")))))

3) =IF(AND(INDEX(Panels_1_1_4,MATCH(Windows!C33,Panels_1_1_4,0)),Windows!C34="Yes"),"Universal Jamb Fin", IF(AND(INDEX(Panels_1_1_4,MATCH(Windows!C33,Panels_1_1_4,0)),Windows!C34="No"), CONCATENATE(Windows!C33," Jamb Fin"), IF(INDEX(IWP_Panels, MATCH(Windows!C33, IWP_Panels,0)), "InsulVIEW Jamb Fin", IF(INDEX(Other_Panels, MATCH(Windows!C33, IWP_Panels,0)), CONCATENATE(Windows!C33, " Jamb Fin")))))


this formula is basically a filter. the spreadsheet is basically an order form for windows, and i'm filtering out wall panel types for the correct jamb fin. i think the problem lays in each "if" statement. each "if" statement compares the "Windows!C33" cell to a different range each time.
Posted by tke857
Member since Jan 2012
12195 posts
Posted on 5/5/17 at 9:06 am to
This post was edited on 5/5/17 at 9:07 am
Posted by Bluefin
The Banana Stand
Member since Apr 2011
13259 posts
Posted on 5/5/17 at 9:06 am to
quote:

1) =IF(AND(Windows!C33=Info!N21,Windows!C34="No"),CONCATENATE(Windows!C33," Jamb Fin"),IF(AND(Windows!C33=Info!N21,Windows!C34="Yes"),"Universal Fin",IF(AND(Windows!C33=Info!N22,C34="No"),"InsulVIEW Jamb Fin",IF(AND(Windows!C33=Info!N22,C34="Yes"),"InsulVIEW Jamb Fin",IF(Windows!C33=Info!N23, "Universal Jamb Fin")))))

2) =IF(C34="No", IF(C33=Info!N21, CONCATENATE(C33, " Jamb Fin"), IF(C33=Info!N22, "InsulVIEW Jamb Fin", IF(C33=Info!N23, "Universal Jamb Fin"))),IF(C34="Yes", IF(C33=Info!N21, "Universal Jamb Fin", IF(C33=Info!N22, "InsulVIEW Jamb Fin", IF(C33=Info!N23, "Universal Jamb Fin")))))

3) =IF(AND(INDEX(Panels_1_1_4,MATCH(Windows!C33,Panels_1_1_4,0)),Windows!C34="Yes"),"Universal Jamb Fin", IF(AND(INDEX(Panels_1_1_4,MATCH(Windows!C33,Panels_1_1_4,0)),Windows!C34="No"), CONCATENATE(Windows!C33," Jamb Fin"), IF(INDEX(IWP_Panels, MATCH(Windows!C33, IWP_Panels,0)), "InsulVIEW Jamb Fin", IF(INDEX(Other_Panels, MATCH(Windows!C33, IWP_Panels,0)), CONCATENATE(Windows!C33, " Jamb Fin")))))



Posted by beerJeep
Louisiana
Member since Nov 2016
35047 posts
Posted on 5/5/17 at 9:07 am to
Good luck.
Posted by TheAlmightySmash
New Orleans
Member since Jun 2014
5479 posts
Posted on 5/5/17 at 9:07 am to
quote:

Please someone do my job for me
Posted by finchmeister08
Member since Mar 2011
35669 posts
Posted on 5/5/17 at 9:07 am to
that's the best response ever
Posted by TheTideMustRoll
Birmingham, AL
Member since Dec 2009
8906 posts
Posted on 5/5/17 at 9:08 am to
Does it give any kind of error message?
Posted by finchmeister08
Member since Mar 2011
35669 posts
Posted on 5/5/17 at 9:09 am to
#n/a
Posted by mikrit54
Robeline
Member since Oct 2013
8664 posts
Posted on 5/5/17 at 9:11 am to
Done.
Posted by DirtyMikeandtheBoys
Member since May 2011
19426 posts
Posted on 5/5/17 at 9:11 am to
Posted by Havoc
Member since Nov 2015
28429 posts
Posted on 5/5/17 at 9:12 am to
Man just google that shite.
Posted by Sasquatch Smash
Member since Nov 2007
24036 posts
Posted on 5/5/17 at 9:12 am to
quote:

i think the problem lays in each "if" statement. each "if" statement compares the "Windows!C33" cell to a different range each time.


Maybe you need some $ someplace to make it stay on the range you want?
Posted by Mr. Hangover
New Orleans
Member since Sep 2003
34509 posts
Posted on 5/5/17 at 9:13 am to
Yo finch, you lost your virginity yet?

I remember that was a big topic of discussion back in the day
Posted by X123F45
Member since Apr 2015
27430 posts
Posted on 5/5/17 at 9:13 am to
The jug is empty.
Posted by SidewalkDawg
Chair
Member since Nov 2012
9820 posts
Posted on 5/5/17 at 9:15 am to
I heard Obama is pretty good with IF statements. Might give him a try.

Posted by TheTideMustRoll
Birmingham, AL
Member since Dec 2009
8906 posts
Posted on 5/5/17 at 9:15 am to
I'm not an Excel programming expert, but my guess is that it's related to all of your parenthetical statements. Probably you didn't close one where you meant to close it.

Sorry for not being able to help more.
Posted by DirtyMikeandtheBoys
Member since May 2011
19426 posts
Posted on 5/5/17 at 9:15 am to
quote:


Maybe you need some $ someplace to make it stay on the range you want?


You too? I also use $ to make my bitches stay in range
Posted by mikrit54
Robeline
Member since Oct 2013
8664 posts
Posted on 5/5/17 at 9:15 am to
Posted by lsuhunt555
Teakwood Village Breh
Member since Nov 2008
38410 posts
Posted on 5/5/17 at 9:17 am to
Posted by Lou Pai
Member since Dec 2014
28126 posts
Posted on 5/5/17 at 9:17 am to
What error are you getting?
first pageprev pagePage 1 of 3Next pagelast page

Back to top
logoFollow TigerDroppings for LSU Football News
Follow us on Twitter, Facebook and Instagram to get the latest updates on LSU Football and Recruiting.

FacebookTwitterInstagram