Complex IF, more than 7 nests

R

Rich Palarea

I have a test that needs to run against 5 pair of data (total number of 25
combinations) using IF and AND. The TRUE result of the IF statement will
perform an array calculation.

The problem is that Excel limits the number of nested IF statements to
seven. How would you go about doing this? I'm sure there is a more
appropriate, powerful way that I'm just not aware of as an intermediate
user.

Example:
Task phrased in plain English:

Step 1: Look at value for This Week's Incentive Bands, New Contract to
determine which array to use for "New Incentives" calculation. I've named
this range "NewBand"
Step 2: Look at value for This Week's Incentive Bands, Benchmark Contract to
determine which array to use for "Old Incentives" calculation. I've named
this range "OldBand"
Step 3: Calculate the result into the cells labeled "Savings"

There are named ranges for each of the white area boxes below. The
convention is New1, New2, New3 and Old1, Old2, Old3, etc.

The result below is working and properly evaluates the following statement
correctly:
{=IF(AND(NewBand="Band 1",OldBand="Band 2"),New1-Old2)}
New Incentives Old Incentives Savings This Week's
Incentive Bands
Band 1 Band 1 New contract Band 1
Next Day Air Letter 10.00% Next Day Air Letter 10.00% Next Day Air
Letter -5.00% Benchmark contract Band 2
Next Day Air 10.00% Next Day Air 10.00% Next Day Air -5.00%
Next Day Air Saver Letter 10.00% Next Day Air Saver Letter 10.00%
Next Day Air Saver Letter -5.00%
Next Day Air Saver 10.00% Next Day Air Saver 10.00% Next Day
Air Saver -5.00%
2nd Day Air Letter 5.00% 2nd Day Air Letter 10.00% 2nd Day Air
Letter -15.00%
2nd Day Air 5.00% 2nd Day Air 10.00% 2nd Day Air -15.00%
3 Day Select 5.00% 3 Day Select 10.00% 3 Day Select -10.00%
Ground CWT 10.00% Ground CWT 0.00% Ground CWT 10.00%
Next Day Air CWT 10.00% Next Day Air CWT 0.00% Next Day Air CWT
10.00%
Next Day Air Saver CWT 10.00% Next Day Air Saver CWT 0.00% Next
Day Air Saver CWT 10.00%
2nd Day Air CWT 5.00% 2nd Day Air CWT 0.00% 2nd Day Air CWT 5.00%
3 Day Select CWT 5.00% 3 Day Select CWT 0.00% 3 Day Select CWT
5.00%

Band 2 Band 2
Next Day Air Letter 37.00% Next Day Air Letter 15.00%
Next Day Air 37.00% Next Day Air 15.00%
Next Day Air Saver Letter 37.00% Next Day Air Saver Letter 15.00%
Next Day Air Saver 37.00% Next Day Air Saver 15.00%
2nd Day Air Letter 32.00% 2nd Day Air Letter 20.00%
2nd Day Air 34.00% 2nd Day Air 20.00%
3 Day Select 20.00% 3 Day Select 15.00%
Ground CWT 20.00% Ground CWT 0.00%
Next Day Air CWT 20.00% Next Day Air CWT 0.00%
Next Day Air Saver CWT 20.00% Next Day Air Saver CWT 0.00%
2nd Day Air CWT 20.00% 2nd Day Air CWT 0.00%
3 Day Select CWT 12.00% 3 Day Select CWT 0.00%

Band 3 Band 3
Next Day Air Letter 56.00% Next Day Air Letter 25.00%
Next Day Air 56.00% Next Day Air 30.00%
Next Day Air Saver Letter 56.00% Next Day Air Saver Letter 25.00%
Next Day Air Saver 56.00% Next Day Air Saver 30.00%
2nd Day Air Letter 43.00% 2nd Day Air Letter 35.00%
2nd Day Air 48.00% 2nd Day Air 40.00%
3 Day Select 25.00% 3 Day Select 22.00%
Ground CWT 25.00% Ground CWT 1.00%
Next Day Air CWT 25.00% Next Day Air CWT 0.00%
Next Day Air Saver CWT 25.00% Next Day Air Saver CWT 0.00%
2nd Day Air CWT 20.00% 2nd Day Air CWT 0.00%
3 Day Select CWT 15.00% 3 Day Select CWT 0.00%

Band 4 Band 4
Next Day Air Letter 57.00% Next Day Air Letter 30.00%
Next Day Air 57.00% Next Day Air 35.00%
Next Day Air Saver Letter 57.00% Next Day Air Saver Letter 30.00%
Next Day Air Saver 57.00% Next Day Air Saver 35.00%
2nd Day Air Letter 44.00% 2nd Day Air Letter 40.00%
2nd Day Air 48.50% 2nd Day Air 43.00%
3 Day Select 26.00% 3 Day Select 25.00%
Ground CWT 26.00% Ground CWT 2.00%
Next Day Air CWT 26.00% Next Day Air CWT 0.00%
Next Day Air Saver CWT 26.00% Next Day Air Saver CWT 0.00%
2nd Day Air CWT 21.00% 2nd Day Air CWT 0.00%
3 Day Select CWT 16.00% 3 Day Select CWT 0.00%

Band 5 Band 5
Next Day Air Letter 58.00% Next Day Air Letter 33.00%
Next Day Air 58.00% Next Day Air 37.00%
Next Day Air Saver Letter 58.00% Next Day Air Saver Letter 33.00%
Next Day Air Saver 58.00% Next Day Air Saver 37.00%
2nd Day Air Letter 45.00% 2nd Day Air Letter 41.00%
2nd Day Air 49.00% 2nd Day Air 45.00%
3 Day Select 27.00% 3 Day Select 27.00%
Ground CWT 27.00% Ground CWT 3.00%
Next Day Air CWT 27.00% Next Day Air CWT 0.00%
Next Day Air Saver CWT 27.00% Next Day Air Saver CWT 0.00%
2nd Day Air CWT 22.00% 2nd Day Air CWT 0.00%
3 Day Select CWT 17.00% 3 Day Select CWT 0.00%
 
S

sbharbour

The way I get around the limitation is to set up the last if to point to
another cell location with another set of nested ifs....
 
M

Mark Rosenkrantz

Rich;

I don't know if u used VBA before, but with a Marco / Procedure in VBA you
can use a Select Case construct to do that job.
That gives you the possibility to test multiple conditions, but you have to
be able to programme VBA.
Your example seems a little bit to complex to me, but this is to give you a
shot into some other direction.

Succes.

Mark.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)
 
O

Otto Moehrbach

Rich
Using formulas for 25 possible combinations is a little much. As Mark
told you, this is simple to do with a Select Case construct in VBA. If you
wish, send me (direct) a small file showing what you have (all 25
combinations) and what you want to happen with each combination and I'll
setup the code for you.
What you showed us in your post makes a lot of sense to you, but you
must understand that you are in the minority. Also, if you were trying to
put your data in columns in your post it got scrambled in the transmission.
If you send me a file, provide as much detail as you can about what you
have, what you want to happen, and most importantly how you would get from
one to the other if you were doing it manually.
Remove "hello" from my address. HTH Otto
 
R

Rich Palarea

Otto - thank you for the generous offer. I've sent you an e-mail containing
more information.

Regards,
Rich
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top