Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

This one is really twisting my noodle... I have 4 nested IF statements that I need to combine into one statement (the parameter choices result in quantities) The proprietary ERP solution into which I need to place this nested IF does not allow AND, OR, etc. Below are the 4 statements and each works 'stand alone' as defined. Essentially I need to make it all the way to the end of the finished statement with the last resolving ZERO. Parameters

For C1 are either 1 or 2

For C2 are 1,2 or 3 (NOTE: parameters 2 and 3 result in the same quantity, hence C2>1 in the formula)

For C3 there are 25 acceptable entries numbers 1 through 25 (NOTE: there are ranges within the last statement of C3)

I've been unable to determine the exact format, bracketing and resolving for Zero. Any help or directions to a tutorial of nested IF WITHOUT the use of AND, OR, NOT, ELSE would be appreciated!

if(C1=1,if(C2=1,if(C3=25,30,if(C3=24,29,if(C3=23,28,if(C3=22,27,if(C3=21,26,if(C3=20,25,if(C3=19,24,if(C3=18,23,if(C3=17,22,if(C3<=11,18,21)))))))))),0),0)

if(C1=1,if(C2>1,if(C3=25,34,if(C3=24,33,if(C3=23,32,if(C3=22,31,if(C3=21,30,if(C3=20,29,if(C3=19,28,if(C3=18,27,if(C3<=11,22,26))))))))),0),0)

if(C1=2,if(C2=1,if(C3=25,60,if(C3=24,58,if(C3=23,56,if(C3=22,54,if(C3=21,52,if(C3=20,50,if(C3=19,48,if(C3=18,46,if(C3=17,44,if(C3<=11,36,42)))))))))),0),0)

if(C1=2,if(C2>1,if(C3=25,68,if(C3=24,66,if(C3=23,64,if(C3=22,62,if(C3=21,60,if(C3=20,58,if(C3=19,56,if(C3=18,54,if(C3<=11,44,52))))))))),0),0)

For C1 are either 1 or 2

For C2 are 1,2 or 3 (NOTE: parameters 2 and 3 result in the same quantity, hence C2>1 in the formula)

For C3 there are 25 acceptable entries numbers 1 through 25 (NOTE: there are ranges within the last statement of C3)

I've been unable to determine the exact format, bracketing and resolving for Zero. Any help or directions to a tutorial of nested IF WITHOUT the use of AND, OR, NOT, ELSE would be appreciated!

if(C1=1,if(C2=1,if(C3=25,3

if(C1=1,if(C2>1,if(C3=25,3

if(C1=2,if(C2=1,if(C3=25,6

if(C1=2,if(C2>1,if(C3=25,6

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

C1= 1, Its choosing HSCASE

C2= 1, Its choosing 68HD

And finally if C3=9, Its choosing CH28

The combination of all three of these "Choices" results in an arbitrary calculated numerical quantity of a specific part being used. In the example given here the answer is:18 Here is a screen shot of the "choice" setup, the formula is placed into a quantity field of a part farther down the screen (not shown):

```
CREATE TABLE answers
( C1 int NOT NULL,
C2 int NOT NULL,
C3 int NOT NULL,
res int NULL,
CONSTRAINT pk_answers PRIMARY KEY (C1, C2, C3));
```

And then fill the table:```
C1 C2 C3 res
1 1 1 18
```

That's a great idea but a lookup table it not possible. Unfortunately, I must work within the confines of the the IF statement ability provided the ERP program.

If your formula says IF C1 = 1 where C1 contains numeric value then it should allow to use the C1 in multiplication. Is it possible?

Lets try to assign specific values randomly selected from your table:

C1 = 2, C2 = 3, C3 = 20 and calculate the formula provided:

if(C2=1, if(C3>=17, C1*(C3+5), if(C3<=11, C1*18, C1*21)), if(C3>=18,

Result: C1*(C3+9) = 2*(20+9) = 58

And you may obtain exactly same value in your 4th if( ... ) which is evaluated for C1=2, C2>1, and C3=20.

Could you please show for which input values is this formula incorrect?

Of course, if you have just text representation of your choices then you cannot use it in arithmetic formulas as stated in your question. In such case C2>1 cannot work. So I believe C1, C2, and C3 are numeric.

It took a while for me to wrap my head around where you're going. Sorry. I believe you're on the right track but It still does not deal with all 4 possible combinations (unless I'm overlooking something) of C1 and C2. I've attached a screen shot of an excel table I made. Column 1 is C3 values, columns 2 through 5 are resultant quantities given the combinations of C3 & C1 & C2. I hope this makes more sense in understanding what I'm looking for. The answer to "where the Zero" goes would be the very last statement ,0) though that is an impossible selection, their code demands resolution of Zero. Sorry, this is a mind bender, to me at least.

Parameters.png

if(C2=1, if(C3>=17, C1*(C3+5), if(C3<=11, C1*18, C1*21)), if(C3>=18, C1*(C3+9), if(C3<=11, C1*22, C1*26)))

if(C2=1, ... the main switch distinguishes between C2=1 and C2<>1

C1 has two possible values 1 and 2 and as these values are perfect as the multiplicands then we may use it for such purpose. Look at your table - results for C1=2 are ALWAYS equal to results for C1=1 multiplied by 2. Thus we don't need any switch based on C1, we just have to multiply the result by C1. Of course, you could reproach (hope this is correct word) the C1 outside the formula which makes it better readable:

C1*if(C2=1, if(C3>=17, C3+5, if(C3<=11, 18, 21)), if(C3>=18, C3+9, if(C3<=11, 22, 26)))

and you may recognize two parts containing C3 based formulas only - one for C2=1 and another one for C2<>1.

C3 has many possible values but the result is almost always linearly dependent on the C3 value in certain ranges. You can recognize certain patterns in your table: We have to add 5 in some cases or 9 in other cases or use some constant which is all implemented as four inner IFs.

And if the zero result raises from impossible parameters selection then we don't need to bother with it any more...

The last question: Did you find any valid parameters combination in my formula which gives result different from your table? I would even say you've calculated the table using my formula :-).

Hope the last column in your table should be for C1=2 & C2 > 1 not C1=2 & C2 > 2.

BTW, you may place the formula into Excel and test various values in C1, C2, and C3 cells. You just have to swap commas for semicolons:

=C1*IF(C2=1; IF(C3>=17; C3+5; IF(C3<=11; 18; 21)); E2IF(C3>=18; C3+9; IF(C3<=11; 22; 26)))

```
if(C1=1,if(C2=1,if(C3=25,30,if(C3=24,29,if(C3=23,28,if(C3=22,27,if(C3=21,26,if(C3=20,25,if(C3=19,24,if(C3=18,23,if(C3=17,22,if(C3<=11,18,21)))))))))),if(C2>1,if(C3=25,34,if(C3=24,33,if(C3=23,32,if(C3=22,31,if(C3=21,30,if(C3=20,29,if(C3=19,28,if(C3=18,27,if(C3<=11,22,26))))))))),0)),if(C1=2, if(C2=1,if(C3=25,60,if(C3=24,58,if(C3=23,56,if(C3=22,54,if(C3=21,52,if(C3=20,50,if(C3=19,48,if(C3=18,46,if(C3=17,44,if(C3<=11,36,42)))))))))),if(C2>1,if(C3=25,68,if(C3=24,66,if(C3=23,64,if(C3=22,62,if(C3=21,60,if(C3=20,58,if(C3=19,56,if(C3=18,54,if(C3<=11,44,52))))))))),0)),0))
```

Good luck.PW

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialHowever, I also have to thank pcelba immensely as he spent allot of time on this and was on to a unique approach (and it would have ultimately worked his way too). Given my timeline for completion of the task I'm forced to use what works right now but I will be returning to study pcelba's approach.

Thank you both, from the bottom of my heart, as this was driving me batty! (I'm trying to give you both 500 points but I've never tried that before UPDATE, no more than 250 each so that is what I'm doing, thanks again)

Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

if(C2=1, if(C3>=17, C1*(C3+5), if(C3<=11, C1*18, C1*21)), if(C3>=18, C1*(C3+9), if(C3<=11, C1*22, C1*26)))

If addition and multiplication is not allowed then you have to make it more complex...

Also, when should be the result = 0 ?