I've got all this data on test runs:

controller  start   end start ts    end ts
1           1:13    1:15    1:00    1:30
1           2:08    2:25    2:00    2:30
4           4:02    4:16    4:00    4:30    
4           4:17    5:35    4:00    6:00
2           4:03    5:39    4:00    6:00

Although each test starts and ends at a specific time, the scheduler for the tests reserves whole half-hour timeslot, so I pad out the times with FLOOR and CEILING.

Each test uses a specific controller, and during the run, no other test can use that controller.

From this data I want to calculate the max number of concurrent controllers in use for a specific time period. To do this, I figured I could calculate the number of controllers in use when a given test starts, since that's the only time the number of controllers in use will increase.

So, my formula for that was: (using 2010's really nice table data syntax)

=COUNTIFS([start timeslot],"<="&[@[start timeslot]],[end timeslot],">"&[@[start timeslot]])

However, because the timeslot rounding introduces overlap between tests that don't actually overlap, countifs will count tests on the same controller as many times as they occur.

I need a way make COUNTIFS count duplicate values only once when they occur in the matching ranges.

Here's an Excel file with some sample data and my formulas:


More info:

I actually constructed an array formula that calculates the numbers I want:

{=SUM(--([start timeslot]<=[@[start timeslot]])*--([end timeslot]>[@[start timeslot]])*IFERROR(1/COUNTIFS([controller],[controller],[start timeslot],"<="&[@[start timeslot]],[end timeslot],">"&[@[start timeslot]]),0))}

The COUNTIFs inside that array formula generate a weighted array of how many times a specific controller occurs within the set of tests that are running when the current test starts. Thus, the sum function will count two tests that use the controller each as 1/2, resulting in counting that controller only once total.

However, my real data set has over 3000 rows, and my array formula brings poor Excel to its knees. (Even my misreporting COUNTIFS function takes ~30 seconds on my old dual-core laptop, so the array formula ticks 1% calculating every minute or so ;_;)

  • Why not eliminate the overlap by subtracting 1 minute from your end ts column when computing it from the end column?
    – Mike Insch
    Jul 20 '11 at 18:30
  • The overlap problem isn't occurring at the start and end of the timeslots; That's taken care of by the [end timeslot], ">"&[@[start timeslot]]. The overlap that concerns me is two tests running on controller 4 will cause the formula to count controller 4 twice if both tests run during another test's start time. I want to catch that controller 4 is being used at that time, but not overreport it. Jul 20 '11 at 19:40
  • OK, why are you creating these formulas for each row, when you only want them per timeslot. It seems like you'd use another table. It's no wonder your formulas are killing you. Also sumproduct doesn't need to ever be an array formula, it handles arrays internally. Jul 22 '11 at 3:21
  • I've been working on this awhile, and it is a tough nut to crack. Jul 22 '11 at 5:52
  • Well, after a few hours, I'm thinking that you'll need to make a few intermediate columns, but I'll get back on this later. Jul 22 '11 at 7:12

From this data I want to calculate the max number of concurrent controllers in use for a specific time period.

Well if you want to do it in Excel, why not use a pivot table. They were design for data summary like this:

(Please notice the countif in the outer right column)

Excel with source data and pivot and countif to the right of the pivot

PS. You can select any data grouping you want (start, end, start ts, end ts, .....)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.