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!
Bookmarks