+ Reply to Thread
Results 1 to 14 of 14

How to exceed 8 max "if" statements

  1. #1
    Registered User
    Join Date
    02-22-2009
    Location
    MORRISTOWN, NJ
    MS-Off Ver
    MICROSOFT 365
    Posts
    60

    How to exceed 8 max "if" statements

    I need to write an IF statement that exceeds the 8 statement maximum. Basically, what I want to say is: IF(U2>K2,1,IF(U2+V2>K2,2.........all the way up to U2...AM2>K2,19) How would I accomplish this? Thanks

  2. #2
    Valued Forum Contributor Rahul Nagar's Avatar
    Join Date
    09-18-2008
    Location
    nasik
    MS-Off Ver
    Excel 2003
    Posts
    465

    Re: How to exceed 8 max "if" statements

    Hello

    you can use vlookup as well, or can you please attach a sample file for more details.
    __________________
    Regards
    Rahul Nagar
    Founder of www.myshortcutkeys.com.


    If you get the answer of your questions then please click EDIT in your original post then click on GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
    Also
    If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to exceed 8 max "if" statements

    Hi,

    Is this required in a single cell? If not you could use the following starting in column U, say U4 and copied across.

    Please Login or Register  to view this content.
    If you want this in a single cell can you upload an example workbook. There's probably a better way of achieving what you want if we can see the problem in context. Add a note or two explaining which cells you want to add and the results you expect.

    Rgds
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    02-22-2009
    Location
    MORRISTOWN, NJ
    MS-Off Ver
    MICROSOFT 365
    Posts
    60

    Re: How to exceed 8 max "if" statements

    Sorry about that, didn't realize you could upload a file... I have attached it. I know that there are DIV/O in some columns (I manually change these for internal purposes). I need the formula calculated in the one column called "CALC WOC" column K (highlighted in yellow). It tests the value of column K against each column starting with U (March-09) all the way to the end (Sep-10), to see how many months it would take to exhaust the inventory value in Cell K. So if Cell U (forecast) is lesser than Cell K (inventory) then it would return the result 1. The formula I have works, but it's limited because I can only have 8 nested If statements maximum, so I need to get around this. Thanks for your help!
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to exceed 8 max "if" statements

    So the logic is - "which column, when summed cumulatively brings the total over the inventory?"

    right?

  6. #6
    Registered User
    Join Date
    02-22-2009
    Location
    MORRISTOWN, NJ
    MS-Off Ver
    MICROSOFT 365
    Posts
    60

    Re: How to exceed 8 max "if" statements

    yes, each column represents MONTHS, so I want to know how many months (since each column represents 1 month) will it take to dissolve the inventory? Then I will take that entire formula and then write *4 to get the amount of weeks.

  7. #7
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to exceed 8 max "if" statements

    How about this:
    M2=MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW($1:$19))))

    Entered with Ctrl+Shift+Enter
    looks like this when done:
    {=MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW($1:$19))))}

    HTH
    Last edited by Cheeky Charlie; 03-04-2009 at 12:36 PM.

  8. #8
    Registered User
    Join Date
    02-22-2009
    Location
    MORRISTOWN, NJ
    MS-Off Ver
    MICROSOFT 365
    Posts
    60

    Re: How to exceed 8 max "if" statements

    Hi, it doesn't work, it only returns the value of "0". Did it work in your test? I did end it with the CTRL+SHIFT+ENTER and the brackets appeared, but the formula returned a zero. Just by highlighting the forecast months (beginning at column U and going across, looking at the bottom of the screen count, I can manually see that 13 months will cover the inventory), therefore it should've returned a value of 13 for the first row.

  9. #9
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to exceed 8 max "if" statements

    Yes, it does work in mine, although the original formula tests up to "the last month before the inventory is exceeded" (i.e. 12)
    For 99% of cases, this would deliver "the first month when the inventory has been exceeded"

    =MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW($1:$19))))+1
    (i.e. 12+1)

    This returns 13 for me in xl03

    CC

    Working example attached
    Attached Files Attached Files
    Last edited by Cheeky Charlie; 03-04-2009 at 12:39 PM.

  10. #10
    Registered User
    Join Date
    02-22-2009
    Location
    MORRISTOWN, NJ
    MS-Off Ver
    MICROSOFT 365
    Posts
    60

    Re: How to exceed 8 max "if" statements

    Okay, that last change you made by adding the +1 worked... now I need to take that number and multiply it by 4 so I convert it into weeks. I tried just writing *4 at the end, but obviously that's wrong

  11. #11
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to exceed 8 max "if" statements

    Not that wrong - it's really pretty logical:
    =(MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW($1:$19))))+1)*4
    Ctrl+Shift+Enter

    You need the brackets because you don't want no of months + (1*4) - you want (number of months + 1)*4...

    HTH

  12. #12
    Registered User
    Join Date
    02-22-2009
    Location
    MORRISTOWN, NJ
    MS-Off Ver
    MICROSOFT 365
    Posts
    60

    Re: How to exceed 8 max "if" statements

    And the last (I promise part is this -- which might change the formula otherwise, I can manually enter the number "72":

    I want the formula to calculate as you instructed above x 4 (to convert into weeks), however anything greater than 18 months would just give the answer 72. If not, again, I can simply manually type 72 in the columns that return an answer of greater than 72. Sorry for the last-minute changes and thanks for everything!

  13. #13
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to exceed 8 max "if" statements

    =MIN((MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW($1:$19))))+1)*4,72)
    CSE
    Sure you can see what this does.

    Yes, it would have been much easier if you had explained all this to start with - I'm not grumpy but please do it that way round next time

    HTH

  14. #14
    Registered User
    Join Date
    02-22-2009
    Location
    MORRISTOWN, NJ
    MS-Off Ver
    MICROSOFT 365
    Posts
    60

    Re: How to exceed 8 max "if" statements

    You are a genius!! It worked! Yeah!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1