# Can I create a formula to look at a date range, set criteria and return an average?

Copper Contributor

# Can I create a formula to look at a date range, set criteria and return an average?

I have a spread sheet that contains rows with a fair bit of info. I'm looking to create a formula that will look for rows with a date within a certain range, then count the number of days between two dates within that row then return an average of all the days on the rows that meet that criteria.

Is this at all possible, I'm tearing my hair out.

Many thanks.

6 Replies

# Re: Can I create a formula to look at a date range, set criteria and return an average?

Before tearing you hair out please provide a sample workbook with the expected result.

# Re: Can I create a formula to look at a date range, set criteria and return an average?

I would second the request from @Detlef_Lewin for a sample of the workbook (without any sensitive data). Post it on OneDrive or GoogleDrive with a link pasted here that grants access.

In the absence of that, if you have Excel 2021 or newer, something based this logic--rendered specific to the column references in your database--should work

=AVERAGE(FILTER(DataToBeAveraged,(Date>="StartDate")*(Date<="EndDate")))

# Re: Can I create a formula to look at a date range, set criteria and return an average?

 Start Finish Outcome 13/01/24 14/03/24 Positive 18/02/24 06/03/24 Neutral 15/11/24 05/03/24 Neutral 15/12/23 21/02/24 Negative 31/01/24 31/01/24 Negative 25/01/24 12/02/24 Neutral 28/07/21 05/12/23 Negative 10/01/24 12/02/24 Negative 23/11/23 19/02/24 Negative 16/11/23 12/02/24 Neutral 16/10/23 31/01/24 Negative 25/10/23 31/01/24 Negative 06/11/23 12/01/24 Negative 24/10/23 11/12/23 Negative 17/10/23 20/12/23 Negative 07/03/23 31/10/23 Positive 15/03/23 20/10/23 Negative 26/06/23 18/10/23 Negative 21/08/23 13/10/23 Negative 25/01/24 12/02/24 Neutral 28/07/21 05/12/23 Negative 10/01/24 12/02/24 Negative 23/11/23 19/02/24 Negative 16/11/23 12/02/24 Neutral 16/10/23 31/01/24 Negative 25/10/23 31/01/24 Negative 06/11/23 12/01/24 Negative 24/10/23 11/12/23 Negative 17/10/23 20/12/23 Negative 07/03/23 31/10/23 Positive 15/03/23 20/10/23 Negative 26/06/23 18/10/23 Negative 21/08/23 13/10/23 Negative

# Re: Can I create a formula to look at a date range, set criteria and return an average?

I hope this helps. I've cut to just the data that is required. Basically I want to know how many days between the start and finish and then work out the average of all those that were positive within set date ranges.

Thank you again.

# Re: Can I create a formula to look at a date range, set criteria and return an average?

A workbook would have been better.

And I suspect you don't know the expected outcome. Which makes every solution a correct solution.

For the number of days.

=[@Finish]-[@Start]

Then create a pivot table.

 Outcome Average of # of days Negative 148,75 Neutral -4,333333 Positive 179

# Re: Can I create a formula to look at a date range, set criteria and return an average?

Basically I want to know how many days between the start and finish and then work out the average of all those that were positive within set date ranges.

I know what each word means, but I am not at all clear on what the sentence means. A great example of what one writer (Lewis Thomas) once described as the maddening ambiguity of the English language.