Nested IF without AND, OR, NOT, ELSE

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!




Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Does the language allow arithmetic operations? If yes then try following formula:

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 ?
BobIaAuthor Commented:
It allows arithmetic operations.  From the look of your solution I believe I lead you astray.  C1 is a "Choice" of either 1 or 2, C2 is a "Choice" of 1,2 or 3 and finally C3 is a "Choice" of 1 through 25.  Therefore, If
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):

What feeds C1, C2 and C3
I think a lookup table will work better in your case. Just have a table answers with the following format:
( C1 int NOT NULL,
  C2 int NOT NULL,
  C3 int NOT NULL,
  res int NULL,
CONSTRAINT pk_answers PRIMARY KEY (C1, C2, C3));

Open in new window

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

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

BobIaAuthor Commented:
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.
Is there an IN operator? Is there a LIKE operator?
BobIaAuthor Commented:
Neither... is it only nested IF that resolves to a number (including Zero) No  "operators" are allowed. Period.
So what's the real value in C1, C2, and C3?  A number or the text? Your question states for the number and then my formulas should calculate correct result except the zeros which I am waiting for clarification and also C3 values greater than 25 are not solved correctly when they occur.

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, C1*(C3+9), if(C3<=11, C1*22, C1*26)))

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.
BobIaAuthor Commented:
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.
So it seems I have to disassemble the formula a little bit...

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)))
How about this?
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))

Open in new window

Good luck.

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 trial
BobIaAuthor Commented:
I'm accepting PWriter as the "best" solution for two reasons, A - because it works in the program directly as a copy-n-past and B- because his solution is more in keeping with the coding philosophy of the ERP vendor (If I move on, who would fix it later?).  I'll have to study his bracketing and resolution of Zero positioning to see where I continually went wrong when I tried that approach but it works perfectly.

However, 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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.