Calculating Average in days of a date range (for multiple rows)
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
Best Answers
-
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, ...................................))
-
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"))
-
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?
Answers
-
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.
-
@KDM Thanks for reaching up.
I'll take a look on it but yes you're right, SS doesn't support plurial AVGs
-
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, ...................................))
-
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?
-
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 , same same :)
-
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?
-
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?
-
Or maybe because I don't yet have data in every rows (see INVALID DATA due to an empty OTD Real Shipping Date)?
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!