- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Easy nested Excel "if" statement: Who can solve it?
Posted on 5/5/17 at 9:05 am
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.
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 on 5/5/17 at 9:06 am to finchmeister08
![](https://media3.giphy.com/media/l1KVb2dUcmuGG4tby/giphy.gif)
This post was edited on 5/5/17 at 9:07 am
Posted on 5/5/17 at 9:06 am to finchmeister08
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")))))
![](https://i.imgur.com/OutFIrV.gif)
Posted on 5/5/17 at 9:07 am to finchmeister08
quote:
Please someone do my job for me
Posted on 5/5/17 at 9:08 am to finchmeister08
Does it give any kind of error message?
Posted on 5/5/17 at 9:12 am to finchmeister08
Man just google that shite.
Posted on 5/5/17 at 9:12 am to finchmeister08
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 on 5/5/17 at 9:13 am to finchmeister08
Yo finch, you lost your virginity yet?
I remember that was a big topic of discussion back in the day
I remember that was a big topic of discussion back in the day
Posted on 5/5/17 at 9:15 am to finchmeister08
I heard Obama is pretty good with IF statements. Might give him a try.
Posted on 5/5/17 at 9:17 am to finchmeister08
What error are you getting?
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 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.
Popular
Back to top
![logo](https://images.tigerdroppings.com/images/layout/TDIcon.jpg)