+ Reply to Thread
Results 1 to 15 of 15

Nested IF's - too many levels of nesting

  1. 03-31-2010, 01:58 PM #1
    sallycanuck
    sallycanuck is offline
    Registered User
    Join Date
    03-31-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Nested IF's - too many levels of nesting

    Hello all,

    I'm looking to output a check time, based on a user-inputted time. I came up with the following statement, but it has too many levels of nesting - can anyone think of a way that I can do this?

    =IF(F2<0,"1:30",(IF(0<F2<(120/1440),"3:30",IF((120/1440)<F2<(240/1440),"5:30",(IF((240/1440)<F2<(360/1440),"7:30",(IF((360/1440)<F2<(480/1440),"9:30",(IF((480/1440)<F2<(600/1440),"11:30",(IF((600/1440)<F2<(720/1440),"13:30",(IF((720/1440)<F2<(840/1440),"15:30",(IF((840/1440)<F2<(960/1440),"17:30",(IF((960/1440)<F2<(1080/1440),"19:30",(IF((1080/1440)<F2<(1200/1440),"21:30",(IF((1200/1440)<F2<(1320/1440),"23:30","1:30")))))))))))))))))))))))

    Thanks!

    Sally
    Last edited by sallycanuck; 04-01-2010 at 10:51 AM.
    Reply With QuoteRegister To Reply
  2. 03-31-2010, 02:07 PM #2
    sallycanuck
    sallycanuck is offline
    Registered User
    Join Date
    03-31-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF's - too many levels of nesting

    I should add that this formula is referencing a cell containing the difference between the inputted time and 1:30 (e.g. 12:05 yields 10:35).
    Reply With QuoteRegister To Reply
  3. 03-31-2010, 02:24 PM #3
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Nested IF's - too many levels of nesting

    Versions of Excel earlier than 2007 are limited to 7 nested functions.It would help if you filled in your profile to say which version you are using.

    An example workbook might help
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  4. 03-31-2010, 02:26 PM #4
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Nested IF's - too many levels of nesting

    Actually, I think there is no need of IF functions at all...

    Try this....

    =30*INT(4*INT(I15*12)-1)/1440
    Reply With QuoteRegister To Reply
  5. 03-31-2010, 02:44 PM #5
    sallycanuck
    sallycanuck is offline
    Registered User
    Join Date
    03-31-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF's - too many levels of nesting

    Hi Roy, I had entered it in my profile (it appears there so far as I can see) - just in case, Excel 2007, and my error message stated there were too many levels of nesting, so I'm assuming that it has a limit, similar to pre-2007 versions.

    zbor, I am using the IF functions to output a time.

    To clarify what I am trying to do, an indivdual inputs a time, and the output is the next time that individual has to perform a check.

    Example:
    Cell D2 (user input): 03/31/10 13:05
    Cell E2: =D2-TIME(1,30,0)
    Cell F2: =IF(F2<0,"1:30",(IF(0<F2<(120/1440),"3:30",IF((120/1440)<F2<(240/1440),"5:30",(IF((240/1440)<F2<(360/1440),"7:30",(IF((360/1440)<F2<(480/1440),"9:30",(IF((480/1440)<F2<(600/1440),"11:30",(IF((600/1440)<F2<(720/1440),"13:30",(IF((720/1440)<F2<(840/1440),"15:30",(IF((840/1440)<F2<(960/1440),"17:30",(IF((960/1440)<F2<(1080/1440),"19:30",(IF((1080/1440)<F2<(1200/1440),"21:30",(IF((1200/1440)<F2<(1320/1440),"23:30","1:30")))))))))))))))))))))))

    My intent is that the time of the next required check will be displayed in A3. So if I completed / inputted my check at 12:05, the cell should display "13:30"
    Reply With QuoteRegister To Reply
  6. 03-31-2010, 02:46 PM #6
    sallycanuck
    sallycanuck is offline
    Registered User
    Join Date
    03-31-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF's - too many levels of nesting

    ...and thanks for the quick formula zbor - it will come in handy otherwise
    Reply With QuoteRegister To Reply
  7. 03-31-2010, 06:50 PM #7
    geoB
    geoB is offline
    Registered User
    Join Date
    03-05-2009
    Location
    6500' in the Sierra Nevada
    MS-Off Ver
    Office 2007, 2010
    Posts
    74

    Re: Nested IF's - too many levels of nesting

    Could you not get the same result using a lookup table where the first column started at 0 and went to 1 in increments of 0.083333 (1/12) and the second column was the hour at which the check should be performed?

    Geo
    Reply With QuoteRegister To Reply
  8. 03-31-2010, 07:16 PM #8
    geoB
    geoB is offline
    Registered User
    Join Date
    03-05-2009
    Location
    6500' in the Sierra Nevada
    MS-Off Ver
    Office 2007, 2010
    Posts
    74

    Re: Nested IF's - too many levels of nesting

    Here's a workbook that may do what you're looking for.

    g
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-01-2010, 02:39 AM #9
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Nested IF's - too many levels of nesting

    Sorry, ROUNDUP instead of INT:

    =3*(4*ROUNDUP(D1*12, 0)-1)/144

    This should work...
    Reply With QuoteRegister To Reply
  10. 04-01-2010, 03:50 AM #10
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Nested IF's - too many levels of nesting

    I wonder if perhaps the below might work for you ?

    =MOD(CEILING(MOD(D2,1),"2:00")+"1:30",1)
    format as h:mm

    the above essentially rounds up the time value in D2 to the next complete two hour value to which a further one hour and thirty minutes is added (ie 12:05 -> 14:00 -> 15:30)

    EDIT: added a final mod to account for those values beyond 22:00 such they return an underlying value of 1:30 rather than 25:30
    Last edited by DonkeyOte; 04-01-2010 at 03:53 AM.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  11. 04-01-2010, 03:59 AM #11
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Nested IF's - too many levels of nesting

    Quote Originally Posted by DonkeyOte View Post
    (ie 12:05 -> 14:00 -> 15:30)
    Form OP inputs 12:05 should be 13:30... so maybe -0:30?
    Reply With QuoteRegister To Reply
  12. 04-01-2010, 04:02 AM #12
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Nested IF's - too many levels of nesting

    Ah, perhaps I missed out the significance of E2 ... in which case I wonder if:

    =MOD(CEILING(MOD(D2,1),"2:00")-"0:30",1)
    format as h:mm

    will suffice ?
    Reply With QuoteRegister To Reply
  13. 04-01-2010, 04:07 AM #13
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Nested IF's - too many levels of nesting

    That said I don't think either yours or mine would work where time is on the boundary (eg 31/3 22:00) presumably this should return 23:30 rather than 21:30 in which case perhaps:

    =MOD(FLOOR(D2+"2:00","2:00")-"0:30",1)
    format as h:mm

    EDIT: I still don't think that's right... ie 1:48 would return 1:30.... I guess we need to better understand what happens between say 1:30 and 2:00 ... should the output be 1:30 or 3:30 ? (etc)

    (sorry for adding to confusion)
    Last edited by DonkeyOte; 04-01-2010 at 04:15 AM.
    Reply With QuoteRegister To Reply
  14. 04-01-2010, 04:21 AM #14
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Nested IF's - too many levels of nesting

    Last one... promise

    =MOD(CEILING(D2-"1:30","2:00")+"1:30",1)

    I think that would work for all based on my interpretation at least (ie 22:00 -> 23:30, 1:48 -> 3:30, 12:05 -> 13:30 etc...)
    Reply With QuoteRegister To Reply
  15. 04-01-2010, 10:49 AM #15
    sallycanuck
    sallycanuck is offline
    Registered User
    Join Date
    03-31-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF's - too many levels of nesting

    That should do it! Thanks for your help all! :D
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1