Forum Discussion
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.
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 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.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.
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")))
