IF THEN Formula Help Request

[perdelta]

I have a report that I download each day to check the time each agent logged in to and out of their phones.


I would like to create a formula that checks a preset range of cells for each agent's name and then references their scheduled start and end times and autofills those times next to their name in the downloaded report.


I hope this makes sense, all apologies if the above information is not clear.

 

[jeremymjp]

If a formula (not VBA), enter this under the LOGIN column:


=INDEX(StartRange,MATCH(AgentsRange,AgentCell,0),1)


And this in the LOGOUT column:


=INDEX(EndRange,MATCH(AgentsRange,AgentCell,0),1)


-StartRange, EndRange & AgentsRange are the outputs of the report.

-AgentCell is the reference to the agent you want to find info for.


Good luck.

 

[jeremymjp]

One important note is to make sure your reference to the Start, End & Agents ranges are exactly the same size, and begin on the same row (unless you choose the entire column as the reference, for which there would be no row reference -- though it's "understood" to be row 1 of the column in that case).


If you have further questions, no prob.

 

[perdelta]

Thanks! One last question if you don't mind. I forgot to mention that people will log in and then log back out from time to time which causes duplicate login entries. That obviously creates an issue with what you had stated about making sure the ranges are exactly the same size.


Is there a fix for this issue?


Thank you for the help Jeremy!

 

[jeremymjp]

You're welcome. Yes, there is a fix. But, would you want to capture their first login, last login, first logout or last logout?


We can fix the range issue by choosing the entire column. Some people would have an issue with that, but it sounds like you don't have a huge record set. So, you'll want to make your Start, End and Agents ranges reference only the COLUMN in which they are in. So, for instance "$C:$C" for a column C reference.


First login:

=MIN((StartRange)*(AgentsRange = AgentCell))


First logout:

=MIN((EndRange)*(AgentsRange = AgentCell))


Last login:

=MAX((StartRange)*(AgentsRange = AgentCell))


Last logout:

=MAX((EndRange)*(AgentsRange = AgentCell))


These are all ARRAY formulas, which is the only "non-traditional" (don't know what else to call it) formula that you can enter into a cell. The difference you want to know is: enter the formula, then "confirm" by pressing <ctrl>+<shift>+<enter> instead of just <enter> (as standard formulas would require).


Good luck.

 

[jeremymjp]

If you know anything about matrices...


Entering an array formula is letting Excel know that you're working with matrices. Think of this problem as (for instance, the first login), telling Excel:


"Tell me the lowest number in the set of login times where the agent matches the agent I'm looking for."


Mathematically, assuming you've got just 8 agents' records in your list, and the agent you're looking for logged in twice, Excel will evaluate it to look something like this:


=MIN((Start1,Start2,Start3,Start4,Start5,Start6,Start7,Start8) * (0, 0, 0, 1, 0, 0, 1, 0))


Then:

=Min((0,0,0,Start4,0,0,Start7,0))


Then, if Start4 is sooner than Start 7:

=Start4

 

[asa]

Hi Perdelta and Jeremy,


Nice solution, but

{MIN({0,0,0,Start4,0,0,Start7,0})}

[code]= 0


For First login and Last login, I think you need to exclude the 0's from the array.  The following will do that with an IF() test.  Boolean values are ignored by most aggregate functions, and if you don't include the If-False parameter to IF, it will just return a Boolean FALSE.


First login:

=MIN(IF(AgentsRange=AgentCell,StartRange))

First logout:

=MIN(IF(AgentsRange=AgentCell,EndRange))[/code]


Last login/Last logout will work as-is, but they will also work with the IF() structure.


I'm working on understanding how to optimize calculations for speed, and I'm curious now... I'm pretty sure that Excel doesn't evaluate any part of an IF expression that it doesn't need to, but that it will fully evaluate all the tests and values if you rely on multiplication and addition for logic... But IF() itself probably has a little overhead, so for simple calculations like this, I'm not sure what formula would be quicker.


Asa

 

[jeremymjp]

Nice, asa. Thanks. I used this tip today for a problem of my own.


"Boolean values are ignored by most aggregate functions..."


Good to know!