+ Reply to Thread
Results 1 to 12 of 12

Nested IF Statement-complex

  1. 02-08-2011, 04:54 PM #1
    Kheldar
    Kheldar is offline
    Registered User
    Join Date
    02-03-2011
    Location
    lawrence, ks
    MS-Off Ver
    Excel 2007
    Posts
    6

    Nested IF Statement-complex

    Hey all, hoping somebody can help me formulate a complex nested IF statement.

    I've got 8 conditions I need a cell to be checked for, they are as followsS=Start Time formatted as 1/1/11 17:35, F=Finish Time formatted the same)

    1. S>6:00, F>14:00=14:00-S
    2. S<6:00, F<14:00=14:00-F
    3. S>6:00, F<14:00=F-S
    4. S<6:00, F>14:00=Rate x 420
    5. S>14:00, F>6:00=F-6:00
    6. S<14:00, F<6:00=14:00-6:00
    7. S>14:00, F<6:00=0
    8. S<14:00, F>6:00=14:00-6:00, F-6:00

    I've tried every way of formulating that I know of, to know avail. Hoping one of you Excel geniuses can help me out.

    Thanks very, very much!


    -Nick
    Last edited by romperstomper; 07-08-2011 at 05:36 PM. Reason: Mark solved
    Reply With QuoteRegister To Reply
  2. 02-08-2011, 05:12 PM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    re: Nested IF Statement-complex

    Hello Nick, this isn't clear to me - are you just checking 2 cells? For condition 1 you have S>06:00, F<14:00...but isn't condition 3 the same....what's the difference there?
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 02-08-2011, 05:14 PM #3
    fervorking
    fervorking is offline
    Registered User
    Join Date
    03-03-2010
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2007
    Posts
    48

    re: Nested IF Statement-complex

    I'm having trouble understanding exactly what you want above. Do the equal signs indicate the TRUE value on the right? So for condition #1, are you looking for something like below?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 02-08-2011, 05:17 PM #4
    Kheldar
    Kheldar is offline
    Registered User
    Join Date
    02-03-2011
    Location
    lawrence, ks
    MS-Off Ver
    Excel 2007
    Posts
    6

    re: Nested IF Statement-complex

    Quote Originally Posted by daddylonglegs View Post
    Hello Nick, this isn't clear to me - are you just checking 2 cells? For condition 1 you have S>06:00, F<14:00...but isn't condition 3 the same....what's the difference there?

    You're correct, I typed condition 1 incorrectly. I've amended my post to reflect the true condition.

    Apologies!
    Reply With QuoteRegister To Reply
  5. 02-08-2011, 05:19 PM #5
    Kheldar
    Kheldar is offline
    Registered User
    Join Date
    02-03-2011
    Location
    lawrence, ks
    MS-Off Ver
    Excel 2007
    Posts
    6

    re: Nested IF Statement-complex

    Quote Originally Posted by fervorking View Post
    I'm having trouble understanding exactly what you want above. Do the equal signs indicate the TRUE value on the right? So for condition #1, are you looking for something like below?

    Please Login or Register  to view this content.
    Correct, I wrote it so if the condition is S>6:00, F>14:00, the true value would be 14:00-S, exactly as you've written.

    Thanks very much!
    Reply With QuoteRegister To Reply
  6. 02-08-2011, 05:25 PM #6
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    re: Nested IF Statement-complex

    If the S is 8:00 and the F is 12:00, which rule applies, 3 or 8?
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.
    Reply With QuoteRegister To Reply
  7. 02-08-2011, 05:36 PM #7
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    re: Nested IF Statement-complex

    Quote Originally Posted by Kheldar View Post
    1. S>6:00, F>14:00=14:00-S
    2. S<6:00, F<14:00=14:00-F
    3. S>6:00, F<14:00=F-S
    4. S<6:00, F>14:00=Rate x 420
    5. S>14:00, F>6:00=F-6:00
    6. S<14:00, F<6:00=14:00-6:00
    7. S>14:00, F<6:00=0
    8. S<14:00, F>6:00=14:00-6:00, F-6:00
    Well, these conditions aren't mutually exclusive, e.g. if the time in S is 07:00 and the time in F is 13:00 that would match the conditions for both 3 and 8, which means you need to decide which order you want to apply these. Logically you might look to apply the conditions in order but that would mean that conditions 4 to 8 can never be applied

    Can you give some examples......
    Reply With QuoteRegister To Reply
  8. 02-08-2011, 05:37 PM #8
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    re: Nested IF Statement-complex

    Can we assume that S < F is always true?
    Reply With QuoteRegister To Reply
  9. 02-08-2011, 05:42 PM #9
    fervorking
    fervorking is offline
    Registered User
    Join Date
    03-03-2010
    Location
    Orlando, Florida
    MS-Off Ver
    Excel 2007
    Posts
    48

    re: Nested IF Statement-complex

    Okay, so I think this is what you're looking for. I think I accounted for every condition, but like others have said, your logic isn't entirely exclusive. Also, I wasn't sure what you meant by "Rate x 420" or "14-6, F-6", so I put them in quotes in the formula. Just type in what you need.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 02-08-2011, 06:57 PM #10
    Kheldar
    Kheldar is offline
    Registered User
    Join Date
    02-03-2011
    Location
    lawrence, ks
    MS-Off Ver
    Excel 2007
    Posts
    6

    re: Nested IF Statement-complex

    Ok, so the whole part of what I'm trying to do is determine a given shift's production based on three pieces of data: Feet run, start time, end time.

    Shifts run 6:00-14:00, 14:00-22:00, 22:00-6:00.

    So, for example, a job ran 6000 feet, started at 8:00, ended at 16:00.

    Total job length would be 8 hrs, average ft per minute would be 12.5, which would mean 1st shift ran 4500 ft, 2nd shift ran 1500.

    So what I need is a conditional IF statement that determines the length of time each shift participated in a certain job, multiplied by the average ft per minute to determine how many feet each shift ran per job.

    I was trying to come up with a logic that would do that.

    Make sense?
    Reply With QuoteRegister To Reply
  11. 02-08-2011, 07:27 PM #11
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    re: Nested IF Statement-complex

    If you have start time and date in A2 and end time and date in B2 then you can use this formula in D2 for the 6-14 hours

    =(INT(B2)-INT(A2))*(14-6)+MEDIAN(MOD(B2,1)*24,14,6)-MEDIAN(MOD(A2,1)*24,14,6)

    and then this similar formula in E2 for the 14-22 hours

    =(INT(B2)-INT(A2))*(22-14)+MEDIAN(MOD(B2,1)*24,14,22)-MEDIAN(MOD(A2,1)*24,14,22)

    clearly all other hours are in the 22-6 bracket so F2 should have this formula to get those hours

    =(B2-A2)*24-D2-E2

    format all three cell as number to show "decimal hours", e.g. 8.5 indicates 8 hours 30 minutes etc.

    If you want the total feet in each period you can then multiply each of those by 60 to get minutes and then by the feet per minute.....you can incorporate that calculation into the hours calculation if you want.......
    Reply With QuoteRegister To Reply
  12. 02-09-2011, 10:34 AM #12
    Kheldar
    Kheldar is offline
    Registered User
    Join Date
    02-03-2011
    Location
    lawrence, ks
    MS-Off Ver
    Excel 2007
    Posts
    6

    re: Nested IF Statement-complex

    That seems to have worked flawlessly DaddyLongLegs.

    Thank you incredibly much, I've been wracking my brain over this for a week. Your Excel-Fu is clearly much stronger than mine.

    Very appreciated!


    -Nick
    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)

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