Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Order of calc. in a nested IF (Excel 2000>)

    Hi all,

    Any comments here?

    Does the order of a multiple nested IF statement matter?
    IE: Would it be better (and allow the IF to calc faster) if you structure it to assess the calculations first and then check for blank cells, or visa versa? Or does this really not matter?

    See attachment for sample!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order of calc. in a nested IF (Excel 2000>)

    Unless you have an extremely large number of cells with such formulas, I don't think it really matters with modern PCs.
    I'd tend to avoid repetition, so I'd use
    <code>=IF(F3=0,0,IF(F3<A3,"QTY NOT ALLOWED",IF(OR(C3="",AND(F3>=A3,F3<=C3)),F3*B3,F3* D3)))</code>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Order of calc. in a nested IF (Excel 2000>)

    How true...thanx for the tip. I never even thought of using OR. (I was not keen on the repetition in the formula anyways! It looks so much better now!)

    PS: While you are at it, could you check to see if you have any suggestions to simplify the SumIf calc to work out the %. I have updated the WB.
    Tx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order of calc. in a nested IF (Excel 2000>)

    As far as I can see, the only error that could occur is that H14 = 0. So you could use

    =IF($H$14=0,0,SUMIF($A$3:$A$11,G17,$H$3:$H$11)/$H$14)

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Order of calc. in a nested IF (Excel 2000>)

    Stunning....its great to bounce these off someone with an expert knowledge like yours. A second opinion is always good, esp. if it improves on mine...which is most often the case <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Tx again!
    Regards,
    Rudi

Posting Permissions

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