Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Shorten Formula ? (Excel 2002/2003)

    Hi

    Can someone help with shortening this Formula please.

    =IF(AND(V62<30),D62,IF(AND(V62>30)*AND(V62<40),40, IF(AND(V62>40)*AND(V62<50),E62,IF(AND(V62>E62)*AND (V62<80),80,IF(AND(V62>80)*AND(V62<100),100,IF(AND (V62>100)*AND(V62<120),120,IF(AND(V62>120)*AND(V62 <180),180,V62)))))))

    Thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Shorten Formula ? (Excel 2002/2003)

    Can you explain in words what the formula is supposed to do?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Shorten Formula ? (Excel 2002/2003)

    Hi Hans

    At this point in time I can't, I will take a closer look and get back to you.


    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Shorten Formula ? (Excel 2002/2003)

    The formula as it is looks very strange, but it can be shortened to

    =IF(V62<30,D62,IF(V62<40,40,IF(V62<50,E62,IF(V62<8 0,80,IF(V62<100,100,IF(V62<120,120,IF(V62<180,180, V62)))))))

    Since I don't know its purpose, its impossible for me to know if it does what you want. But I think you should either change the single occurrence of E62 in the formula to 50, or else change 50 to E62.

    It may be possible to shorten the formula further using MATCH and INDEX.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Shorten Formula ? (Excel 2002/2003)

    Hi Hans

    Thank you for your efforts, I will try it against the old code and see if I get the same result, I have just inherited this workbook and am trying to unravel it.

    Thanks again

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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