Array Formulas for Faster Calculations ?

Hello,

I've hourly data extracted from a transactional system which is used for a scatter plot chart.

I'm trying to summarize this data extract to load it into a transactional database.
I need some help to build array formulas in Excel (instead of the SUBTOTAL function I've used) that can calculate results faster.

As the data spans for 24x7x365 (and by hour), the number of records is high (more than 100,000+ rows of data is generated per year).

Can some one help code the array formulas to summarize for calculating the average LogIn-Logout time and Standard Deviation per day & per month ?
The SUBTOTAL function takes too much time to calculate and the workbook calculations take long to refresh when filters are used.

I also tried coding the formulas using SUMPRODUCT & SUMIFS function, but unable to zero in on the appropriate formula that can calculate results faster.

Can someone help ?



Sarang

Srinivas' array formulas doesn't seem to work properly. Refer the attached file below.
I'm also looking for more dynamic formulas that can adjust to the worksheet data range automatically.

Can someone help code the Array formulas correctly ?

Bumping Thread ..

you expand the solutions you have with the condition (LogInLogOut__secs>0)
=SUMPRODUCT(--(INT(EventTime)=$J17)*(LogInLogOut__secs)*(LogInLogOut__secs>0))/SUMPRODUCT((INT(EventTime)=$J17)*(LogInLogOut__secs>0))

=STDEV.P(IF(INT(EventTime)=$J17,IF(LogInLogOut__secs>0,(LogInLogOut__secs),"")))

These formulas work perfectly fine.

One question though - Does the average and STDEV.P calculation be reset automatically at the start of every month ?
Will the formulas need to be adjusted for the calculations to restart every month ?

Bumping thread ..

You would be quicker making changes and seeing if the formulas change. Even if you dont full understand the formulas you could manually change a few values in the data and observe the result. Part of the aim of the forum is to encourage learning, and this would be a small step along the way. Your next question might be that you want to change something else as a result of this experimenting, and you could have asked that earlier and so got a solution earlier.

They are all based on the values in j17:j44 , if you change the month you would have to change the values in these cells, but that would not take long to do. You could even just make j18 =j17+ 1 and copy down. Then you would only need to adjust the first value when it was a new month

Thanks for all the help !! You have saved my time a lot by making Excel do faster calculations..