Average value between two dates.

Abhiraj Joshi

Hi, I have generated a report from different project sheets.

I have a column (C) which has dates when a particular task is completed. Column D calculates the time taken to complete that task (Turn Around Time - TAT).

I want to calculate the average TAT taken to complete a task in the month of Feb.

I am not able to put two date conditions in averageif formula (condition 1 - (>=01,02,2020) & condition 2- (<=29,02,2020)

What is the solution?

DJB

It's hard to give a specific solution without seeing how your sheet is set up, but you could create a formula where you do sumifs (adding all the TAT within the criteria) divided by countifs (counting the number of tasks in same criteria).

Or you could have a separate column in your report that assigns a period to each row. Then you would just have one criteria for your averageif formula. Example: =YEAR([Date]@row) + "-" + MONTH([Date]@row) which gives me a result 2020-03 for March 2020. I then use that period for various reports.

Abhiraj Joshi

Interesting suggestions. Will try them.

Thank you!!

Paul Newcome

You can use two conditions within an AVERAGEIF function as long as they are both based on the same range by using an AND statement.

NOTE: In my example formula below, I am going to reference month number and year, but you can use it to reference a start date and end date if you prefer.

=AVERAGEIF([Date Column]:[Date Column], AND(criteria 1, criteria 2), [Average Column]:[Average Column])

=AVERAGEIF([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2020), [Average Column]:[Average Column])

Jennifer Lenander

@Paul Newcome - you are genius! I'm hoping you might be able to help me with my average formula.

I have a new sheet where I'm trying to calculate the average of the SLA column, per month and year for several months and back to 2019. Below is the sheet where I'm pull the data from but I'm not sure how to set-up my formula. I tried =AVERAGEIF({SLA}, {RequestMonth}, "1", {RequestYear}, "2019") but I get an incorrect argument set. I have a feeling I'm overthinking this. Any brilliant ideas?

Paul Newcome

@Jennifer Lenander AVERAGEIF is only built for a single range/criteria set. To incorporate multiple range/criteria sets, you will need to use an AVG/COLLECT combo instead.

=AVG(COLLECT({SLA}, {RequestMonth}, 1, {RequestYear}, 2019))

Pam Grant

Hello @Paul Newcome (a.k.a. genius)

I am attempting to apply your formula to average the turnaround days for the entries that occurred in the current fiscal year.

Your formula: =AVERAGEIF([Date Column]:[Date Column], AND(criteria 1, criteria 2), [Average Column]:[Average Column])

=AVERAGEIF([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2020), [Average Column]:[Average Column])

My formula =AVERAGEIF({Closed Date}, AND(>DATE(2021, 3, 31), <DATE(2022, 4, 1), {Turnaround Days})

{Closed Date} = is the date the request was closed

{Turnaround Days} = is the number of days to turnaround a request based on a subtraction of the date we closed it from the date it was requested. (The earliest date I want is April 1, 2021 and the latest will be March 31, 2022)

The average should return "4" but I'm getting "0"

Here is an example of my data:

Thank you for your time and expertise!

Paul Newcome

@Pam Grant Try using "@cell" references inside of the AND statement.

=AVERAGEIF({Closed Date}, AND(@cell > DATE(2021, 3, 31), @cell < DATE(2022, 4, 1), {Turnaround Days})

If that doesn't work, how is your Closed Date column populated. Is it set as a date type column?

Travis Horton

I have a similar issue as the other users in this thread trying calculate an average between a date range. I don't see a final resolution in any of the comments so I'm hoping to get some help. I've tried multiple iterations of the formula below but they all return "0%". In the formula I'm trying to get an average of the ECD Percentage in the month of January 2022 for Angie Blue.

=AVERAGEIF({Install Date}, AND(<=DATE(2022, 1, 31), >=DATE(2022, 1, 1), AND({OM3}, "Angie Blue", {ECD Percentage})))

Abhiraj Joshi

Hi @Travis Horton

We added additional columns (e.g. Installation Month, Installation Year) and used MONTH and YEAR column formula. We use the those columns in our formulas.

However, you can use the following formula without adding those columns.

=AVG(COLLECT({ECD Percentage}, {Order Manager}, "Angie Blue", {Installation Date}, AND(YEAR(@cell) = 2021, MONTH(@cell) = 1)))

Let me know if it works.

Paul Newcome

@Travis Horton If you are calculating for a specific month in a specific year, you can use the below (no need to create separate columns for month and year):

=AVG(COLLECT({ECD Percentage}, {Install Date}, AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 1)), {OM3}, "Angie Blue"))

If you want to be able to specify dates, the syntax is the same but you would replace the YEAR and MONTH functions with DATE functions (be sure to use the @cell references as well).

=AVG(COLLECT({ECD Percentage}, {Install Date}, AND(@cell <= DATE(2022, 1, 31), @cell >= DATE(2022, 1, 1)), {OM3}, "Angie Blue"))

Travis Horton

Thank you Abhiraj and Paul. I'll report back on my results after using your suggestions.

Travis Horton

This worked! Thank you!!

=AVG(COLLECT({ECD Percentage}, {Install Date}, AND(@cell <= DATE(2022, 1, 31), @cell >= DATE(2022, 1, 1)), {OM3}, "Angie Blue"))

Paul Newcome

Happy to help. 👍️

Paul Rosas

@Paul Newcome

I'm having trouble with this as well. Here is what I have so far and this is working.

The formula I have on the bottom is working fine for what I'm trying to achieve, which is getting an average of SF per labor hour for a certain week and certain machine.

However, I need to incorporate another machine. I need to use this formula for both the "Yukon II" as well as "Storm"

I'm just not sure how to make this formula with two Machines labeled in the criteria.

Paul Newcome

@Paul Rosas The criteria for the Machine:Machine range would be an OR statement:

OR(@cell = "Yukon II", @cell = "Storm")