Monthly Turnover Time

cjleon

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.

Paul Newcome

What type of widget are you wanting to use and how would you want the data displayed within the widget?

cjleon

I am wanting to use a chart widget to display a monthly average of turnover time. A bar chart would be sufficient.

Paul Newcome

Are you able to provide some screenshots of your sheet(s)?

cjleon

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.

Paul Newcome

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?