How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Scott Your Own Question

Scott
Scott, MIT Graduate
Category: Homework
Satisfied Customers: 3040
Experience:  MIT Graduate (Math, Programming, Science, and Music)
3546829
Type Your Homework Question Here...
Scott is online now
A new question is answered every 9 seconds

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: 6 months ago.
Category: Homework
Expert:  swmcdonnell replied 6 months ago.
Hi, my name is*****'m taking a look at the file you sent now.
Expert:  swmcdonnell replied 6 months ago.
Have you ever used a pivot table?
Expert:  swmcdonnell replied 6 months ago.
You're going to have to help me understand what's in the Excel file a little better.
Expert:  swmcdonnell replied 6 months 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 6 months 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 6 months 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 6 months ago.
OK, I'm looking at it. I will have a few questions, hold on.
Expert:  swmcdonnell replied 6 months ago.
the schedule is scheduled time in/out, the LILO is actual time in/out and the Combo Breaker compares the two?
Customer: replied 6 months ago.
that is correct.
Expert:  swmcdonnell replied 6 months ago.
When yoiu match the LILO to the schedule, how do you know if you're matching the right entries?
Customer: replied 6 months 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 6 months ago.
The second formula I hd in my initial question I thought would do this but doesnt
Expert:  swmcdonnell replied 6 months ago.
What's the ultimate objective? What information are you trying to get at?
Expert:  swmcdonnell replied 6 months 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 6 months ago.
Or is it enough to look at a day at a time?
Customer: replied 6 months ago.
Yep.
Expert:  swmcdonnell replied 6 months 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 6 months 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 6 months ago.
for each 30-minute block, is it enough to know how many minutes the person worked during that time period?
Customer: replied 6 months ago.
As long as the right login time is referenced
Expert:  swmcdonnell replied 6 months ago.
Have you ever considered putting the data into a database?
Customer: replied 6 months ago.
I hadn't. I was hoping just to tweak the formula in the combo breaker.
Expert:  swmcdonnell replied 6 months 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 6 months ago.
Okay. I can look into getting a database set up
Expert:  swmcdonnell replied 6 months 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 6 months ago.
Okay.
Expert:  swmcdonnell replied 6 months ago.
How many agents do you have?
Customer: replied 6 months ago.
About 300
Expert:  swmcdonnell replied 6 months 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 6 months 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 6 months ago.
Okay. I will look into it.
Expert:  swmcdonnell replied 6 months ago.
Even if it's in Access, you're still going to have to write a little code.
Expert:  swmcdonnell replied 6 months ago.
Sorry, I wish I had a simpler solution for you.
Expert:  swmcdonnell replied 6 months ago.
Ultimately, I think you want something like the attached. Let me know if I can help you any further.

Related Homework Questions