+ Reply to Thread
Results 1 to 15 of 15

Complex If/Then Statements

  1. #1
    Registered User
    Join Date
    12-27-2013
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2013
    Posts
    42

    Complex If/Then Statements

    I actually have two issues I'd like to ask about.

    The first issue (which I'm not convinced is possible):
    Our information is given to us as a list of manager names with cases that need to be reviewed. When it comes time to bill for the time spent reviewing the cases, we bill the each customer for the total number of cases. However, most managers cover more than one customer. So, for the sake of assisting some of my less mathematically inclined coworkers, I am trying to figure out how to get Excel to break up the information by manager and from there, by customer.

    Example:

    Manager 1:
    Customer 1
    Customer 1
    Customer 1
    Customer 2
    Customer 2
    Customer 3
    Customer 3
    Customer 3


    Manager 2:
    Customer 4
    Customer 5
    Customer 5
    Customer 5
    Customer 6

    So, how do I get the spreadsheet to recognize the difference between Manager 1 and Manager 2 and from there, add up the amount of time spent on each Customer?


    The second issue ties into that as well. We can only bill a maximum of 8 minutes per case (rounded up to the nearest 15 minute increment after totaling the time). If we take less than 8 minutes, then we bill that actual amount. Example: 3 cases were reviewed with completion times of 15 minutes, 7 minutes, and 5 minutes. So, for our billing purposes, the 15 minutes is capped off at 8. 8+5+7= 20, rounding up to the nearest 15 minute increment, meaning that we would bill that customer for 3 cases totaling 30 minutes. So, what I'm looking for on this one is some way to recognize when the number needs to be capped off (so when x>8) and total up that time spent per customer in accordance to the requirements listed above in Issue 1. Bonus points if you can integrate the 15 minute round up into the equation as well.

    Sorry for the long post. Any help would be appreciated!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex If/Then Statements

    Hi and welcome to the forum

    It would be lots easier to help if we could see samples (in a workbook) if the data you are working with, and what you want.

    For the 1st part, IF the manager name is in it's own column, you could probably use =sumif() =sumif(range-with-manager-name,manager-name,range-to-sum-values)
    If the names are not in their own column, we could probably get around that too.

    for the 2nd part, it really would help to see samples...
    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-27-2013
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: Complex If/Then Statements

    Sample workbook is attached. What I'm looking for is rudimentarily outlined in the "Billing" tab. Please keep in mind that the number of TAM's (managers) that each person reviews will vary and always be more than just two.
    I had an equation in one of my older iterations of the "Billing" tab that generated the maximum billable time (in minutes) in relation to a specified number of cases "=ceiling(([# of cases]*8),15)". This, of course, did not take into account the actual time taken for cases that took less than 8 minutes to evaluate, making it less accurate than what we require.

    Please let me know if I need to supply any additional information, or if this is sufficient supplementation for my original post.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex If/Then Statements

    How do you arrive at 30 in C9?

    This gives you the count of M1/Cust1, and might get you headed in the right direction...
    =COUNTIFS('Wave 1'!$I:$I,Billing!$C$8,'Wave 1'!$A:$A,Billing!$B$9)

  5. #5
    Registered User
    Join Date
    12-27-2013
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: Complex If/Then Statements

    41 minutes and 12 minutes both get capped off at 8 each. So 3+8+6+8= 25, rounded up to the nearest 15 makes 30.

    When you say M1, is that indicating Manager #1, or the specific cell?

  6. #6
    Registered User
    Join Date
    11-20-2013
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Complex If/Then Statements

    The attached spreadhsheet is something I did that may give you an idea how to split all the case officers up. In cells A2:D51, I had it pull the data from another worksheet, then I sorted it out using an array in the other visible portions. I just copied in a bunch of names so you get the idea how it sorts out the data.

    You could probably do some vlookup formulas once you integrate the billing which seems like it shouldn't be too hard.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-27-2013
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: Complex If/Then Statements

    That's definitely a step in the right direction. I would just have to apply that "unique judges" formula to enough cells to accommodate a high number of managers to account for that unknown variable, right?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex If/Then Statements

    Try this, using a helper column (I used V)
    in V2, copied down...
    =MIN(N5,8/60/24)
    Then for the calcs, use this, copied down...
    =MAX(15,MROUND(SUMIFS('Wave 1'!$V:$V,'Wave 1'!$I:$I,Billing!$C$8,'Wave 1'!$A:$A,Billing!B9)*60*24,15))=MAX(15,MROUND(SUMIFS('Wave 1'!$V:$V,'Wave 1'!$I:$I,Billing!$C$8,'Wave 1'!$A:$A,Billing!B9)*60*24,15))

    change the "Billing!$C$8" part for each new Manager

  9. #9
    Registered User
    Join Date
    12-27-2013
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: Complex If/Then Statements

    Did you post that in the "Wave 1" tab or "Billing" tab? I'm assuming Billing, but wanted to check before I ruin everything forever.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex If/Then Statements

    I put the 1st 1 in the Wave1 tab, and the 2nd 1 in the billing tab. And just to make you feel better, if you had put them in the wrong place, it just would not have worked (the 2nd formula could go on either sheet though, but it makes sense to put it on the 2nd sheet)

  11. #11
    Registered User
    Join Date
    12-27-2013
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: Complex If/Then Statements

    Sorry. Now I'm thoroughly confused. Put the two formulas in. The 1st was put in Z2 on the Wave 1 tab and the cell generated "0.002083". I put the second equation into D2 on the Billing tab and changed to "V" specifications to "Z" and it just came back with "False"... Was that supposed to happen?
    This is a bit beyond my knowledge of Excel formulas so I'm not entirely sure what to expect/change and how to properly do so. So you may need to spell it out a bit for me... Sorry.

  12. #12
    Registered User
    Join Date
    11-20-2013
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Complex If/Then Statements

    I think you might be better off using a pivot table. I know what they can do, but I have only tried to use one once and didn't have much luck. Good luck.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex If/Then Statements

    Im so sorry, I didnt notice that I copied the 2nd formula twice . It should just be...
    =MAX(15,MROUND(SUMIFS('Wave 1'!$V:$V,'Wave 1'!$I:$I,Billing!$C$8,'Wave 1'!$A:$A,Billing!B9)*60*24,15))

    As far as getting "0.002083" for the 1st formula, that is just what the time would look like if it was formatted as a number, instead of time. Time is actually just a decimal of 1 (day), so 06:00 AM is actually 0.25, 12 noon is 0.5 etc)

    So, on Wave1, in V5 (or wherever you want to put it), copied down...
    =MIN(N5,8/60/24)

    I just noticed that is the "answer" is less then 7 above 15 etc, it rounds down, so change to this on Billing in C9, copied down...
    =MAX(15,MROUND(SUMIFS('Wave 1'!$V:$V,'Wave 1'!$I:$I,Billing!$C$8,'Wave 1'!$A:$A,Billing!B9)*60*24+7,15))

  14. #14
    Registered User
    Join Date
    12-27-2013
    Location
    Irving, Texas
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: Complex If/Then Statements

    So, that equation is intended to provide the properly rounded and summed up times, correct?

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex If/Then Statements

    Yes, that is correct, see the attached
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-30-2012, 01:42 PM
  2. Excel 2007 : Complex IF Statements
    By JonathanKilleen in forum Excel General
    Replies: 1
    Last Post: 11-27-2009, 06:03 AM
  3. Excel 2007 : Complex IF Statements.
    By JonathanKilleen in forum Excel General
    Replies: 1
    Last Post: 11-27-2009, 05:29 AM
  4. complex SUMPRODUCT statements
    By cvillemtnman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2008, 06:18 PM
  5. complex if statements in excel
    By Julieeeee in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2005, 05:06 PM

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