Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Angie Hatfield
Angie Hatfield ✭✭✭✭
edited 10/05/17 in Archived 2017 Posts

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!

 

AVG 1,2,3.jpg

AVG 1,2,3.jpg

Comments

  • Jason Anderson
    edited 10/05/17

    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

     

     

     

    chrome_2017-10-05_18-02-07.png

    chrome_2017-10-05_18-02-20.png

    chrome_2017-10-05_18-10-18.png

    chrome_2017-10-05_18-10-31.png

    chrome_2017-10-05_18-14-23.png

  • Angie Hatfield
    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

This discussion has been closed.