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

Copper Contributor

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

@BenAppleton 

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

 

@BenAppleton 

 

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")))

 

StartFinishOutcome
13/01/2414/03/24Positive
18/02/2406/03/24Neutral
15/11/2405/03/24Neutral
15/12/2321/02/24Negative
31/01/2431/01/24Negative
25/01/2412/02/24Neutral
28/07/2105/12/23Negative
10/01/2412/02/24Negative
23/11/2319/02/24Negative
16/11/2312/02/24Neutral
16/10/2331/01/24Negative
25/10/2331/01/24Negative
06/11/2312/01/24Negative
24/10/2311/12/23Negative
17/10/2320/12/23Negative
07/03/2331/10/23Positive
15/03/2320/10/23Negative
26/06/2318/10/23Negative
21/08/2313/10/23Negative
25/01/2412/02/24Neutral
28/07/2105/12/23Negative
10/01/2412/02/24Negative
23/11/2319/02/24Negative
16/11/2312/02/24Neutral
16/10/2331/01/24Negative
25/10/2331/01/24Negative
06/11/2312/01/24Negative
24/10/2311/12/23Negative
17/10/2320/12/23Negative
07/03/2331/10/23Positive
15/03/2320/10/23Negative
26/06/2318/10/23Negative
21/08/2313/10/23Negative

@BenAppleton 

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.

@BenAppleton 

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.

OutcomeAverage of # of days
Negative148,75
Neutral-4,333333
Positive179

 

 

@BenAppleton 

 

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.