# Thread: New to Nesting (Excel 97 SR-2)

1. ## New to Nesting (Excel 97 SR-2)

I'm trying to solve a situation where the results in columns B and F vary with what's contained in column D. I'm having difficulty with the syntax in creating a nested IF statement to get my results. Here's what I need: If the value in column D is under 5000, I need the values in the other two columns to be 20% less or more respectively. If the column D entry is between 5,000 and 10,000, I need a 15% variance. If it's 10,001 or more, the variance is only 10%. I just need a model to follow in one column and I can apply it to the other. Thanks.

2. ## Re: New to Nesting (Excel 97 SR-2)

I'm not sure that I follow you exactly, but does <pre>=IF(D2<5000,D2*80%,IF(D2>10001,D2*90%,D2*85%) ) </pre>

in B2 help. If so maybe <pre>=IF(D2<5000,D2*120%,IF(D2>10001,D2*110%,D2*11 5%)) </pre>

in F2 will also help.

Andrew C

3. ## Re: New to Nesting (Excel 97 SR-2)

Thanks, with just a little tweaking, I think I'm there. I tried this and it appears to work:

=IF(D2<5000,D2*80%,IF(D2>5000<10000,D2*85%,IF(D2>1 0000,D2*90%)))

4. ## Re: New to Nesting (Excel 97 SR-2)

There are a couple of approaches, and a VLOOKUP table for the variances is probably a neater solution if you should need to modify the variance levels in the future. For a quick-and-dirty, in cell B2 enter
=-CHOOSE(1+(D2>4999)+(D2>9999),0.2,0.15,0.1)
in cell F2
=CHOOSE(1+(D2>4999)+(D2>9999),0.2,0.15,0.1)
copy down

5. ## Re: New to Nesting (Excel 97 SR-2)

Oh ... in that case:

=CHOOSE(1+(D2>4999)+(D2>9999),0.8,0.85,0.9)*D2

=CHOOSE(1+(D2>4999)+(D2>9999),1.2,1.15,1.1)*D2

6. ## Re: New to Nesting (Excel 97 SR-2)

Your tweaked formula seems to return FALSE (due to D3>5000<10000) in B3.

I'm not clear on whay you needed to tweak it, but 2 IFs should be adequate to make a choice from 3 options.

Andrew C

7. ## Re: New to Nesting (Excel 97 SR-2)

You're right, Andrew. Perhaps I was too quick on the trigger. I also found that John's suggestion works very nicely. Thanks to both for the prompt and effective help!!

8. ## Re: New to Nesting (Excel 97 SR-2)

One more quirk I just came across:

IF the value in the Underrun column (B6) is less than the value of the Overrun column in the row immediately above (F5)
THEN: B6 should = F5+1

How do I incorporate this without botching the formula?

9. ## Re: New to Nesting (Excel 97 SR-2)

See if the attaced workbook does what you want.

10. ## Re: New to Nesting (Excel 97 SR-2)

Very, very close. It's an elegant solution, and I very much appreciate it. I realized when testing, however, that I need to add one final condition:

The IF function should ONLY apply when the Item No. column, Column A, is the same throughout.

For example, if Row 5 is Widget 1 and Row 6 is Widget 2, then the IF condition should NOT apply and no changes should occur.

Is that too complex, or is such a formula still feasible to write?

11. ## Re: New to Nesting (Excel 97 SR-2)

OK, try the attached workbook.

12. ## Re: New to Nesting (Excel 97 SR-2)

Thank you so much! All of you on Woody's Lounge have been real life-savers today.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•