0

At the end of each month I have to calculate the concurrent usage of a bunch of applications each day over the previous month.

Using the ideas shared in other posts I have created a formula that effectively counts the concurrent sessions of each application.

The problem I have is that each month there could be 60,000+ session lines and 20+ different applications. To calculate the concurrent usage for each application I have to manually find the range of cells used by each app and adjust the formulae, which is very tedious!

I wondered if it would be possible to create a Macro that (once I have sorted the sessions into application and start date) could for each change in application name calculate the overall range for that application and enter the formulae?

The SUMPRODUCT formula is like this:

=SUMPRODUCT(--($D$4:$D$7<=D6),--($E$4:$E$7>=D6))

enter image description here

5
  • short answer is YES you probably can write a macro to do this. long answer is: I doubt anyone is going to give you a long answer and just write the code for you. – PeterH May 7 '19 at 10:03
  • so if I got this right, your range is bound by the first row of a product and the last row of the product. – Forward Ed May 7 '19 at 12:37
  • Thank you PeterH. I was hoping someone might point me in the right direction, like - how do I get a macro to identify\calculate each change in the application name (col I),and\or how to calculate\create the appropriate formulae. – GDay May 7 '19 at 12:39
  • Hi Forward Ed yes that's right. And that range might only be one cell or thousands in any given month. Thanks in advance. – GDay May 7 '19 at 12:40
  • going to look at INDEX function. an important thing to note is that index retrurns a cell address which in turns displays the contents of that reference. as a result you can can actually make a formula to define a range by doing something similar to INDEX:INDEX – Forward Ed May 7 '19 at 12:40
0

Something that may be helpful for you is to use a named range. That name range could be set up to cover all the rows of your data and also be dynamic. Something like =COUNTA(A:A) would tell you how many rows you are using including header provided there are no blank cells in Column A:A. If you had blanks before your header row you could adjust the formula to hard code in the blank rows (assume 6) by adjusting the formula to:

=COUNTA(A:A)+6

or

=COUNTA(A:A)+ROW(A7)

A7 would be the first row of your header line and would account for the all the rows above it being blank. so your named range formula could be:

=$I$1:INDEX(I:I,COUNTA(A:A))

So in order to define the range you want to look for your SUMPRODUCT formula, you could use another array formula. Now this is going to bog down your system somewhat as it is going to be an array operation over a lot of lines repeated a lot of times. I get in to the ballpark number of calculations when done. Lets assume you did this for your app name in column I and the name range was APP_LIST.

First you want to find the starting row of your band of data. This can be done with the aggregate function. The aggregate function is made up of a lot of different functions. you are going to be interested in 14 and 15. They will sort the results of their array calculations either ascending or descending order depending on which you use. You can then also tell aggregate to ignore errors, give it criteria to consider through a math operation, and then which position in the sorted results you want to obtain. The AGGREGATE function could look like the following:

AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)

The above will tell you what row you are interested in, but you need an address. In order to get the address you need to embed the AGGREGATE function in an INDEX function. You could use INDIRECT, but it is a volatile function and will cause unnecessary calculations when working with your sheet. So the address of the first cell in your range will be:

=INDEX(D:D,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1))

To get the last row in the range, its basically the same formula but you want AGGREGATE to sort in the opposite direction.

=INDEX(D:D,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))

Now since you need this for both column D and column E you would would repeat the process

=INDEX(E:E,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1))
=INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))

So your range for column D becomes:

INDEX(D:D,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(D:D,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))

and your range for column E becomes:

INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))

Now you just need to dump that into your SUMPRODUCT

=SUMPRODUCT(--(INDEX(D:D,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(D:D,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))<=D2),--(INDEX(E:E,AGGREGATE(15,6,ROW(APP_LIST)/(APP_LIST=I2),1)):INDEX(E:E,AGGREGATE(14,6,ROW(APP_LIST)/(APP_LIST=I2),1))>=D2))

Place the above in J2 and copy down

Now I don't really recommend going this route due to the number of excessive calculation. With AGGREGATE you are going to got through 60K rows for each aggregate call. in your sumproduct that AGGREGATE is called 4 times. that is 240K calculations in one cell alone. Now you are also going to wind up copying this down 60K rows, that means 60K * 240K = 14.4 million calculations just from the AGGREGATE. You are also going to have the SUMPRODUCT calculations to deal with as well. Be prepared that it may take some time for the calculations to finish.

1
  • Thanks Forward Ed. There plenty of food for thought there! – GDay May 7 '19 at 14:33

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.