Using AVERAGEIFS with dates

Question

Hi there,

Important aspects of my sheet are as follows:

• I have a column recording the name of each type of test performed
• Corresponding with each type of test, there are separate columns for indicating

1. date the test was initiated,
2. date test was requested,
3. date test result was received,
4. date test result was communicated

I would like a formula to be able to calculated the average time taken per month for a task to be performed for a given. For example, how long on average (in days) did it take between 1 June 2020 - 30 June 2020 for X test to be requested from when it was initiated.

I would eventually like to represent these data with a plot of average time per month for a given test from 1 June 2020 - 30 June 2021 (seems reasonably straightforward)

Could I please have some guidance with this? Is AVERAGEIFS even the correct approach to the question?

I have the logic in my head by struggling to figure out how to do this!

With thanks,
ryan

Answer 1

I would create a Pivot table from the data, add a DAX formula and calulate End-Start to get the duration, used this as value field, use the Start as row field, dates are grouped automatically, done. Optional: Create a Pivot chart.

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like https://www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1

If you want to upload it to OneDrive, it is important to follow these steps:
Zip your file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5
Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your Zip file.
After uploading, right click the file and choose share.
Click Get a Link.
Copy the link and paste it here.

Andreas.