• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MATCH help

W

Weow

New Member
I am using index/match to locate the next item in a list that matches criteria. The list is showing when users in a call center are logging in and out of the phone queue. For every LOGIN in Column J, column K has a formula that should find the next LOGOUT in Column J associated with the same extension. For some reason, about 35% of them return N/A even though the instance does exist and can be found if I change the formula to be static.


The formula is =IF(AND(J2="LOGIN",H2),INDEX(E3:E$6000,MATCH((I2+1.00001),I3:I$6000,0))-E2,"") and essentially looks in column I for a uniquely numbered entry for logout. I use the WHOLE NUMBERS to represent unique entries for each extension, then I use .xxxx for the extension, and then I use .0000x for whether it is a LOGIN or LOGOUT. so to find a logout, it adds 1.00001. for instance, the login at the very top of the file shows 1.71151 in Col. I, because it is the 1st instance of 7115, and the 1 at in the 0.0000x place says it is a login. a logout would be a 2 afterwards, so to find the very next LOGOUT for extension 7115, it needs to find 2.71152.


I hope this makes sense. I feel like its kind of confusing. Maybe theres an altogether better way. Basically, for each extension I want to know the elapsed time between each LOGIN/LOGOUT. Only the green highlighted lines mean anything, the rest is all junk data produced by the report. So it needs to locate the next UNIQUE logout. I attached the file so someone might be able to see the #N/A errors and tell me why they are happening so randomly, or provide me with a better way to do what I need to.


https://www.dropbox.com/sh/hwf6bt6zh3x5a8l/u0D1MkKdcH/Agent%20Availability.xlsx
 
Thanks for your question Weow.. I have looked at the file and I could not understand why this error is happening either. When I evaluate portions of the formula, I get correct result. I think the error may be due to complicated nature of your formulas.


Since it felt like too much work to go thru all formulas, I copied your data to a new sheet and solved the logout - login time problem in my own way. With the solution, there are fewer #N/As, but these are all due to a user having logged in and not logged out (at least there is no entry for the same).


See the file here:


http://img.chandoo.org/playground/Agent%20Availability-weow.xlsx
 
Hi ,


As an alternative , this seems to work , entered as an array formula , using CTRL SHIFT ENTER :


=IF(AND(J2="LOGIN",H2),INDEX($E$2:E$6000,SMALL(IF($I$2:I$6000=(I2+1.00001),ROW($2:$6000)),1)-1)-E2,"")


The following part :


MATCH((I2+1.00001),I3:I$6000,0)


has been replaced by this part :


SMALL(IF($I$2:I$6000=(I2+1.00001),ROW($2:$6000)),1)


The relative references have been replaced by absolute references ; I think this will not matter , since the values in column E are in ascending order , and there is no chance of duplicates in both the login times and logout times.


Narayan
 
Thank you so much!


I've been doing a LOT of excel work with my job lately and your blog has been a HUGE help. And this one issue was making me crazy.
 
You must log in or register to reply here.
Back
Top