Calculate MIN AVERAGE MAX number of days between two periods/dates

[Aoleone#]

Hello

I have a dataset of transactions (80K rows). I need to audit to find out the average and maximum number of days elapsing between the intended settlement date - Column G and the Actual settlement date - Column AG. I further want to split this between days elapsing due to Business delays or Customer delays which can be identified in Column AF of the transaction data. MTS represents business delays and EXT represents customer delays.

Looking at the volume of data (80K rows) I thought of using a pivot table but had issues with presenting the difference in the dates . Any ideas on this will be much appreciated.

Thanks

[ben_hensel]

Can you create a helper column in the data table that's something like "Settlement Date Difference" that is just "=AG2 - G2" down the whole range? From there, it would be pretty straightforward to create a PT.

Formulaic builds would also work, but you would be repeating that AG-G calculation a lot, and would probably force you to use all array formulas instead of any SUMIFS/COUNTIFS or whatever, so it would inflate the recalc time in an inefficient way.

[¯\_(ツ)_/¯]

As Ben mentioned, in AJ2 insert the formula

=AG2-G2

Fill that down to the bottom of your data.

Then in your summary sheet, you can use MAX(IF, MIN(IF and AVERAGE(IF functions to get the results you need. (If you had Excel 2016+ you could use MAXIFS, MINIFS and AVERAGEIFS.) The Excel 2013 versions are array formulas. After you type or edit an array formula you must press CTRL+SHIFT+ENTER to confirm, not just ENTER.

Examples:

Summary!F6 (minimum for Business delays):

=MIN(IF(TransactionData!$B$2:$B$30=$C6,IF(TransactionData!$AF$2:$AF$30="MTS",TransactionData!$AJ$2:$AJ$30)))

Fill that down to F9.

Summary!G6 (minimum for customer delays):

=MIN(IF(TransactionData!$B$2:$B$30=$C6,IF(TransactionData!$AF$2:$AF$30="EXT",TransactionData!$AJ$2:$AJ$30)))

Fill that down to G9.

Summary!H6 (average for business delays):

=AVERAGE(IF(TransactionData!$B$2:$B$30=$C6,IF(TransactionData!$AF$2:$AF$30="MTS",TransactionData!$AJ$2:$AJ$30)))

Fill down to H9.

For the MAX functions, use the same formulas you used for MIN and AVERAGE, just replace that word with MAX

Etc...

(Note: The average formulas will return a DIV/0 error if no non-zero values exist for those criteria. You can hide these, of course, or adjust the formula to accommodate them.)

[Aoleone#]

Many thanks Guys. I adopted the PT and it works alright.