• 100% Satisfaction Guarantee
Category: Homework
Satisfied Customers: 3040
Experience:  MIT Graduate (Math, Programming, Science, and Music)
3546829
Scott is online now

# I am using an array formula and I need it to pull times

### Customer Question

I am using an array formula and I need it to pull times for each specific group of times based on an agent. When i do just the Min formula I get times, however I need to only have my formula look at times that match up to a specific individual ID here are the formulas I am using: =MIN(IF(LILO!\$H\$2:\$H\$6>Combo_Breaker!D7,LILO!\$H\$2:\$H\$6)) this one works pulling time. By addition to this formula looks like this =MIN(IF((LILO!\$A\$2:\$A\$7=C11*LILO!\$H\$2:\$H\$6>Combo_Breaker!D11,LILO!\$H\$2:\$H\$6)) does not work to just have the report only look at the times that match a given agent. Is there any way using this formula to have it just pull over the times from the LILO for the given agent.
Submitted: 1 year ago.
Category: Homework
Expert:  swmcdonnell replied 1 year ago.
Hi, my name is*****'m taking a look at the file you sent now.
Expert:  swmcdonnell replied 1 year ago.
Have you ever used a pivot table?
Expert:  swmcdonnell replied 1 year ago.
You're going to have to help me understand what's in the Excel file a little better.
Expert:  swmcdonnell replied 1 year ago.
Well, please chat me back when you get back online. I can definitely help you, I just need to understand a little better what exactly you're trying to do and what's on the spreadsheet.
Customer: replied 1 year ago.
I have used a pivot table before. This is an attendance report. When the macro runs it moves data from the schedule and lilo tabs to the combo breaker tab. I need the column for the lilo 1 in the. Combo breaker to.show only login times that match the agent in the lilo tab. For instance same dude is showing a login on the combo breaker of 3:12 when it hold be 3:14
Customer: replied 1 year ago.
The current formula is pulling the closest login times but I need it to pull times that match the agent name only
Expert:  swmcdonnell replied 1 year ago.
OK, I'm looking at it. I will have a few questions, hold on.
Expert:  swmcdonnell replied 1 year ago.
the schedule is scheduled time in/out, the LILO is actual time in/out and the Combo Breaker compares the two?
Customer: replied 1 year ago.
that is correct.
Expert:  swmcdonnell replied 1 year ago.
When yoiu match the LILO to the schedule, how do you know if you're matching the right entries?
Customer: replied 1 year ago.
That is my struggle. My formula is pulling over times, but I am unsure what to add to make sure it is only looking at the login times for the scheduled person
Customer: replied 1 year ago.
The second formula I hd in my initial question I thought would do this but doesnt
Expert:  swmcdonnell replied 1 year ago.
What's the ultimate objective? What information are you trying to get at?
Expert:  swmcdonnell replied 1 year ago.
For example, are you trying to see by each 30-minute segment how much time the person actually spent? And then just summarize that?
Expert:  swmcdonnell replied 1 year ago.
Or is it enough to look at a day at a time?
Customer: replied 1 year ago.
Yep.
Expert:  swmcdonnell replied 1 year ago.
OK, so let's look at jboggie. Scheduled 5am to Noon in 30-minute increments. He logged in a 3:12, almost 2 hours early, and then logged out at 6:00. How does that get recorded? Does the time he worked when not on the schedule show up separately?
Customer: replied 1 year ago.
The time not scheduled is disregarded. The 3:12am to 6am would be applied to the 30 minute blocks that fall within that range.
Expert:  swmcdonnell replied 1 year ago.
for each 30-minute block, is it enough to know how many minutes the person worked during that time period?
Customer: replied 1 year ago.
As long as the right login time is referenced
Expert:  swmcdonnell replied 1 year ago.
Have you ever considered putting the data into a database?
Customer: replied 1 year ago.
I hadn't. I was hoping just to tweak the formula in the combo breaker.
Expert:  swmcdonnell replied 1 year ago.
We could probably do that and get it to work this one time, but the next time you try to do it, you're going to have to change it around. You're hardcoding absolute cell references in your formulas, and they probably won't be accurate when the data changes.
Customer: replied 1 year ago.
Okay. I can look into getting a database set up
Expert:  swmcdonnell replied 1 year ago.
On the spreadsheet, first your data should be sorted by date and time, in ascending order. I think that's part of the issue.
Customer: replied 1 year ago.
Okay.
Expert:  swmcdonnell replied 1 year ago.
How many agents do you have?
Customer: replied 1 year ago.
Expert:  swmcdonnell replied 1 year ago.
Wow, OK, I was going to ask if it would be feasible to put each agent on its own tab, but I don't think it is.
Expert:  swmcdonnell replied 1 year ago.
It's possible to write some Excel VBA code to do it, but it would be better if it were in an Access database or something similar. The reason is that, in a database, it's easy to go person by person and to know you're only dealing with the information about that person. In Excel, it's harder to know where someone begins and ends. But it is possible by writing a VBA program.
Customer: replied 1 year ago.
Okay. I will look into it.
Expert:  swmcdonnell replied 1 year ago.
Even if it's in Access, you're still going to have to write a little code.
Expert:  swmcdonnell replied 1 year ago.
Sorry, I wish I had a simpler solution for you.
Expert:  swmcdonnell replied 1 year ago.
Ultimately, I think you want something like the attached. Let me know if I can help you any further.