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

Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

@KDM Thanks for reaching up.

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

• Options

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?

• Options

@Paul Newcome , same same :)

• Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!