Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #3
    New Lounger
    Join Date
    Apr 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #7
    New Lounger
    Join Date
    Apr 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    New Lounger
    Join Date
    Apr 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Attached Files Attached Files

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    See if the attaced workbook does what you want.
    Attached Files Attached Files
    Legare Coleman

  10. #10
    New Lounger
    Join Date
    Apr 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    OK, try the attached workbook.
    Attached Files Attached Files
    Legare Coleman

  12. #12
    New Lounger
    Join Date
    Apr 2002
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •