0

Simple scenario: We have three computers in an office. Employees come in and out all day, using them. We want to know if we can reduce the office to two computers, by finding out if three people are using computers at the same time.

I have 3 columns: A is the name of the PC, B is date/time that a user starts a session, C is session end time.

I'm trying to figure out an equation that'll tell me how much overlap I have for these sessions. I've Googled around a bit, but haven't found something that fits this scenario.

Improve this question
1
  • Could you provide a sample data or screenshot about your problem? This will better for use to help you.
    – Lee
    Nov 22, 2018 at 9:06

1 Answer 1

Reset to default
0

You can use sumproduct to count the number of occurrences in a column where a time is being used in the range of a time in another row.

Based on the set up of your data, you could use the following formula in D and copy down. Adjust the range to suit your needs. Since SUMPRODUCT is a function that performs array calculations, it is recommended that full column not be used.

=SUMPRODUCT((B1>=$B$1:$B$10)*(B1<=$C$1:$C$10))

Results of 3 tell you that three computers are in use as of the start time of the row where the 3 appears.

Results of greater than 3 tell you that something in your schedule is "wonky" because apparently that number of people are trying to use 3 computers at the same time.

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .