complex if statement

**young_eeyore** · 12-11-2015, 05:50 PM

I want to create an "if" statement evaluating two cells. One cell would have three different options. The second cell has a number to be evaluated by if statements. I want the result of the second cells evaluation be put in a third cell. The following formula works perfectly, with one exception: =CONCATENATE(IF(D3="STYLE A",IF(E3<19.735,19.375,IF(E3<22.375,22.375,IF(E3<25.375,25.375)))),IF(D3="SINGLE",IF(E3<22.375,22.375,IF(E3<25.375,25.375))),IF(D3="BAR",IF(E3<12,12,IF(E3<14,14,IF(E3<18,18,IF(E3<26,26,IF(E3<32,32,IF(E3<36,36,IF(E3<42,42))))))))). The result is the true/false to each part of the if statement. I just one the true answer.

**Richard Buttrey** · 12-11-2015, 05:54 PM

Hi and welcome to the forum,

Generally we find with these type of requests its easier for us to understand what you want if you can upload some representative examples covering all permutations in a workbook and manually add the results you expect. In a narrative form explain the rules your are applying to get the results. Sometimes we can find more efficient methods but we need to understand the goal.

**young_eeyore** · 12-11-2015, 06:09 PM

That makes so much sense. The attached excel file is what I am working with. Essential what I want is a result based on limitations of a real life product. If a particular VT profile is chosen (D3) and a width is given (E3), I want to know what width I have to have to order to accommodate the required width. For example if Style A is chosen and the width is 25, I want 25.375 in the resultant cell. Or if Bar is chosen and the width is 13, I want a result of 14.

**young_eeyore** · 12-11-2015, 06:15 PM

The "if" statements for each type work individually, but I don't know how to add the complexity of an additional requirement. The result in the above formula is compounded for each part. Because of the CONCATENATE function, the result of all three if formulas are displayed. I only want the result of the true statement. If it is style a, then 19.375, 22.375, etc. or if single, then, 22.375 or 25.375 or if bar, then...

**Richard Buttrey** · 12-11-2015, 06:16 PM

Hi,

But what's the relationship between the Style and Width that results in the examples you give. i.e. how does a Style A and a Width of 25 result in 25.375, and how does BAR and a width of 13 translate to 14? i.e what are the calculations you are making?

Is there a lookup table we need to understand?

**young_eeyore** · 12-11-2015, 06:24 PM

It's not a calculation. The attached is specification for one type of countertop. Not all countertops are the excat dimensions of the give specifications. I can order a bigger countertop than needed and cut it down. I want to be able to input the desired size and have the result tell me which one I need base on the style.

**Richard Buttrey** · 12-11-2015, 06:54 PM

Originally Posted by young_eeyore

It's not a calculation.

That's why I asked you if a lookup table is involved - and from your pdf it seems there is.

You need to build a LOOKUP Table. List the styles down a table of rows and the widths in ascending order across the columns, (e.g. 18, 21, 24 etc,) then in the matrix of cells add the results, e.g. 19.375, 22.375, 25.375.

Then you can use an

Formula:

Please Login or Register  to view this content.

formula.

StyleA

**young_eeyore** · 12-11-2015, 07:20 PM

I guess my "if" statement is to complex. I'll have to see if I can figure out what data is needed in the LOOKUP table to give me the results I want.

**Richard Buttrey** · 12-11-2015, 07:37 PM

Originally Posted by young_eeyore

I guess my "if" statement is to complex. .

...Indeed. That's often the case. If you find yourself adding more than five or six IF statements, then is the time to consider better options. The better option is usually a lookup table or, as in this case probably a lookup matrix for an INDEX(MATCH()) combination.

complex if statement

LinkBack

Thread Tools

Rate This Thread

Display

complex if statement

Re: complex if statement

Re: complex if statement

Re: complex if statement

Re: complex if statement

Re: complex if statement

Re: complex if statement

Re: complex if statement

Re: complex if statement

Thread Information

Users Browsing this Thread

Similar Threads

Need Help with Complex If Statement (for me anyway)

[SOLVED] Help with complex If statement please

[SOLVED] Complex IF AND OR statement

Complex IF Statement?

Complex IF Statement

Complex If Statement

Complex IF Statement

Bookmarks

Bookmarks

Posting Permissions