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