Monthly Turnover Time
Hello, I am working to create a widget on a dashboard. In order to do this, I want to get data between a start date and end date (turnover time) per month to show monthly progress. I have tried by finding the month and year but I'm not sure how to attach the turnover time days with its corresponding month/year
.
.
.
=COUNTIFS([Month of Creation Dates]1:[Month of Creation Dates]244, =1, [Year of Creation Dates]1:[Year of Creation Dates]244, =2018)
.
.
.
I then tried to find the sum but it gives a massive number no where close to what I'm looking for (I also realize this next formula has no year aspect but I'm not sure how to incorporate it quite yet)
.
.
.
=SUMIF([Month of Creation Dates]1:[Month of Creation Dates]244, [Month of Creation Dates]1:[Month of Creation Dates]244 = 1, [Turnover Time]1:[Turnover Time]244)
.
.
Any suggestions/solutions appreciated!
Thank you for your time.
Comments
-
What type of widget are you wanting to use and how would you want the data displayed within the widget?
-
I am wanting to use a chart widget to display a monthly average of turnover time. A bar chart would be sufficient.
-
Are you able to provide some screenshots of your sheet(s)?
-
Sorry for the late response! Didn't get a notification.
The formula I have now is manual but I was looking for something more automated where I don't have to put the cell name in order for it to be counted.
-
Long story short, you are going to want to use and AVG(COLLECT( formula. The first range is the range of numbers to be averaged. You can then specify criteria range/criteria within the rest of the COLLECT function.
Long story long...
See the below screenshot. It may have a little more too it than what you are looking for, but it will cover pulling at the very least numbers from a specific month and/or year and can be adjusted in many different ways to include numerous amounts of criteria depending on how specific of a result you are looking for.
Basically what I do is create a table. I name the first column as "Year" and then every column after that is the corresponding month name from January on to December.
I then list the year I am wanting to pull for in row 1 of the year column and then continue across row one with the month numbers 1 - 12.
If I have multiple sets of data to compare against the same criteria, I list those down the Year column starting in row 2.
I then use the following:
.
=AVG(COLLECT($Count:$Count
The name of the column where the numbers I want to average is located.
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell),
Will skip over any cells in the Count column that does not contain a number (gets rid of the #INVALID DATA error).
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date
My Date column I am using to search against.
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(
I am looking at multiple criteria for the same range (month and year), so I specify AND to keep from having to duplicate the range entry.
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0))
Tells the formula to look at the year, but if there is no year to use 0. This will put a 0 in place of the year if the date cell is blank so it will essentially skip over blank date cells without throwing an error on the YEAR function.
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1
Refers to the cell where I entered my year in the table.
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0))
Same as the YEAR but looking at the month instead.
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0)) = January$1)
Refers to row 1 of whatever column the formula is in (note the $'s used throughout to lock references for drag-filling).
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0)) = January$1)))
Closing out the AVG and COLLECT functions using )) then drag-filling on down the row will give you your monthly averages.
.
=AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell), Date:Date, AND(YEAR(IFERROR(@cell, 0)) = $Year$1, MONTH(IFERROR(@cell, 0)) = January$1), $Type:$Type, @cell = $Year@row))
For the example in the screenshot I also broke it down by type, but pulling for year and month alone is detailed in the previous steps above.
.
Does this help any?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!