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

[BenAppleton]

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.

[#2]

@BenAppleton 

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

[#3]

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

[#4]

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

@BenAppleton 

[#5]

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.

[#6]

@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.

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

[#7]

@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.