Formula to get averages from one column if another column is checked

Angie Hatfield

Hi All!

I have 2 date columns, Processing Start Date & Processing Turnaround Time, then a column to show the NETWORKDAYS, (labeled Average Setup Time), of those two columns. Then I have a checkbox column (labeled New Setup)and another column to convert the checks into 1, 2, 3, etc.(labeled Setups).

What I need to do now is figure out the average NETWORKDAYS for those Setups converted to 1.

I have been fiddling with this on and on. Not too good with IF yet, but learning. I could give you a long list of the formulas I keep trying.

Screenshot attached. I may only have one column left available, so if there is any way to calculate this without adding another one, that would be great!

I should also mention that the averages will be in the parent rows, which are separated into months and then one average at the top for all months.

Thank you very much!

 

Jason Anderson

I think I understand what you are looking to do. 

Here's an example I built and tested. (screen shots)

Columns 

Start (date)

End (date)

Networkdays

=NETWORKDAYS([Start Date]55, [End date]55)

Average setups (parent row)

=IFERROR("Average setups:" + AVG(CHILDREN()), "no setups")

child rows have 

=IF(Checkbox55 = 1, [Month Name]55)

Checkbox column

 

If a checkbox is checked the child cell next to it copies the value from Networkdays

The parent of that column then calculates the AVG of all the children of that column. If there are no children it displays "no setups".

See screens...

 

Edit: I thought of another option that uses one less column. See my formula testing sheet here. (at the bottom of the sheet)

https://app.smartsheet.com/b/publish?EQBCT=a0123120a0b747aebd8162322e85f01d

 

 

 

Angie Hatfield

You Sir are a genius and the hero of my day. That absolutely worked and was such a simple formula.

=IF([New Setup]3084 = 1, [Processing Turnaround Time]3084)

I can't thank you enough. Having this metric will make my COO very happy to see this on her dashboard.

Thank you very much. I hope you have a pleasant Friday and a great weekend!

Angie