Revise current formula to add IF Statement

Sherry Fox

Hello again! First, thanks for looking, and thanks even more for potentially helping, it is greatly appreciated. Okay, first, I have a working formula (wooohooo)! Okay, I now need an additional IF Statement added to my current formula, and this is where it gets tricky. The first screenshot is where my formula will go. The second screenshot is my source file. In the "Release Driver Column, the first "child" is empty when it is not my dept (Process Transformation), there is a blank in the first "child" for the other projects. I need the formula revised to where if Release Driver = Process Transformation, than the formula calculates as is. However, if that cell is blank, than that stage (not a 0) is excluded from calculating the running average. For those instances Plan Develop Running Average will show as a blank cell, and the Running Average will begin at the 2nd stage, because the release driver is not blank in that column. Thanks again!

Sheet: Date Metrics-Single Line

Column: Running Average (there are 4 stages each with this column)

Formula: =IFERROR(AVG(COLLECT([Plan / Develop - Duration]:[Plan / Develop - Duration], [Plan / Develop - End Date]:[Plan / Develop - End Date], @cell <= [Plan / Develop - End Date]@row)), "")

Paul Newcome

Try something like this:

=IF([Release Drive]@row <> "", original_formula)

Sherry Fox

@Paul Newcome

That is not quite working. As I mentioned, the formula is going to be in the first screenshot (Date Metrics-Single Line). So since this displays the "parent" line only the blanks are not seen. The source file (second screenshot) display the child rows, which are the different stages. The first stage is Plan / Develop, which is populated for my team, but not for others. Can we incorporate the formula to look at the 1st child under the parent? And how would this formula be altered (if it is required) for each project These parents in the 2nd screenshot are on rows are: 1, 7 and 13. So their 1st child rows are: 2, 8, and 14. Working with parent/child rows like this is new to me.

Paul Newcome

I'm not sure I follow. The formulas in the first screenshot look like they are pulling in data from the same sheet (except for the dates column).

Sherry Fox

@Paul Newcome ,

I just found out that all other projects do not use the Plan / Develop phase, only us. So I can base the IF Statement on our team. I changed it to:

=IFERROR(IF([Release Driver]@row = "Process Transformation", AVG(COLLECT([Plan / Develop - Duration]:[Plan / Develop - Duration], [Plan / Develop - End Date]:[Plan / Develop - End Date], @cell <= [Plan / Develop - End Date]@row)), ""), "")

Sherry Fox

@Paul Newcome ,

You are correct. However I would have to access the second sheet to determine whether the first child is blank or not. But since I found out that ONLY a "Release Driver" of "Process Transformation" would include the Plan / Develop stag, I can just use the IF Statement that I originally requested your help with. I have added it, and it works great, thanks!!!!