Calculating Average in days of a date range (for multiple rows)

Julien Remo

Hi there,

I have a Date range on a Data base sheet (for a lot of rows). I have the difference in Days for the gap between these two dates. In order to create a metric on a Dashboard, on another sheet I trying to get the average in days of these multiple rows and... don't get it!

I have to select multiple criteria so here is the formula I have without any good results.

=AVERAGEIFS({DATABASE Project completed}, 1, {DATABASE Work Type}, [Primary Column]@row, {DATABASE Expected}, >=DATE(2020, 6, 1) <= DATE(2020, 6, 30), {DATABASE country}, USA1, IFERROR({DATABASE Difference date} = 1))

I also tried with the =COUNTIFS, no better results.

I think the problem is that SS can't figure out what to do if there is a blank cell (for exemple if there's no End of Installation Date).

Thanks guys!

Julien

Paul Newcome

AVG/COLLECT does definitely solve this issue. When building it out, the syntax would be that of a SUMIFS.

=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ...................................))

Paul Newcome

Here is a quick tweak. See if it works. If it does not, let me know and we can dive a little further into it.

=AVG(COLLECT({DATABASE Difference date}, {DATABASE Project completed}, 1, {DATABASE Work Type}, [Primary Column]@row, {DATABASE Expected}, AND(@cell >=DATE(2020, 6, 1), @cell <= DATE(2020, 6, 30)), {DATABASE country}, "USA1"))

Paul Newcome

Do you mean you are getting the same error? If so, that means we would need to take a look at your data. It means that no rows meet all of the criteria you have listed. Is USA1 a cell reference (row 1 in the [USA] column, or is it specific text?

Kelly Moore

Hello Julien,

I think at least one issue is that smartsheet currently does not support AverageIfS (plural). Being in the same conundrum, I found @Paul Newcome 's post to solve my problem.

Julien Remo

@KDM Thanks for reaching up.

I'll take a look on it but yes you're right, SS doesn't support plurial AVGs

Paul Newcome

AVG/COLLECT does definitely solve this issue. When building it out, the syntax would be that of a SUMIFS.

=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ...................................))

Julien Remo

Thanks @Paul Newcome. Here's the formula right now :

=AVG(COLLECT({DATABASE Difference date}, {DATABASE Project completed}, 1, {DATABASE Work Type}, [Primary Column]@row, {DATABASE Expected}, >=DATE(2020, 6, 1) <= DATE(2020, 6, 30), {DATABASE country}, USA1))

And the result is : DIVIDE BY ZERO

Any idea why?

Paul Newcome

Here is a quick tweak. See if it works. If it does not, let me know and we can dive a little further into it.

=AVG(COLLECT({DATABASE Difference date}, {DATABASE Project completed}, 1, {DATABASE Work Type}, [Primary Column]@row, {DATABASE Expected}, AND(@cell >=DATE(2020, 6, 1), @cell <= DATE(2020, 6, 30)), {DATABASE country}, "USA1"))

Julien Remo

@Paul Newcome , same same :)

Paul Newcome

Do you mean you are getting the same error? If so, that means we would need to take a look at your data. It means that no rows meet all of the criteria you have listed. Is USA1 a cell reference (row 1 in the [USA] column, or is it specific text?

Julien Remo

Yes I get the same error.

The USA1 is a specific text in the sheet (that I use as a Metric Sheet to create Charts) in order to refer to the right country in my Database. I did it like this because I couldn't find any way to refer to a country in the coloumn directly in the Database. Maybe that is the problem?

Julien Remo

Or maybe because I don't yet have data in every rows (see INVALID DATA due to an empty OTD Real Shipping Date)?

Paul Newcome

OK. So if USA1 is specific text, then it needs quotes, but based on your screenshot it looks like it is just USA.

=AVG(COLLECT({DATABASE Difference date}, {DATABASE Project completed}, 1, {DATABASE Work Type}, [Primary Column]@row, {DATABASE Expected}, >=DATE(2020, 6, 1) <= DATE(2020, 6, 30), {DATABASE country}, "USA"))

The invalid data error is coming from 2 parts. The first part is because of how you have your date criteria.

=AVG(COLLECT({DATABASE Difference date}, {DATABASE Project completed}, 1, {DATABASE Work Type}, [Primary Column]@row, {DATABASE Expected}, AND(@cell >=DATE(2020, 6, 1), @cell <= DATE(2020, 6, 30)), {DATABASE country}, "USA"))

The second part is going to be because the error is present within one of your ranges. If you fix this in your source sheet using an IFERROR to either generate a blank or a zero or something that is not an error in combination with the above adjustments, you should be up and running.

Julien Remo

Thanks a lot Paul for your help!

I'll be able to try this on tomorrow and of course, I'll keep you posted but I think we're closing up on this since it seems to make sense.

Have a great day!

J.