Help with Formula logic

[Guest]

I have this formula in Sheet2
=IF(OR(ISTEXT(L2),ISBLANK(L2)),"",IF(OR($M2>'Sheet1'!$A2+1,ISBLANK($M2)),NETWORKDAYS('Sheet1!$A$2,$L2),"")) to store the data in the W column.
And then I use disaply it in the sheet1 by using "=AVERAGE('Sheet2'!W3:W141)"

I intend to combine two formula together so I do not need to create many
columns in Sheet2 because the A colcumn in the Sheet1 increases.
I tried to use SumIf and Sumproduct, however, I have a hard time to make it
right.
Any suggestion will be highly appreciated.

 

[Earl Kiosterud]

I haven't attempted to combine your formulas; someone surely will. But I
wanted to offer this.

For reliability and maintainability, it's better to keep parts of a
complicated algorighm separate. It's easier to make changes, fix problems
(they can be a nightmare when time has passed and the formula isn't so
familiar, or someone else has to start from scratch with its logic), and do
assurance on the process. You could use a hidden column for column W, and
refer to it with your second formula. One argument against it is code
efficiency (how much storage it requires, and how long it takes to execute).
In pretty much all of these cases, that's virtually nil. Development time,
reliability and maintainability are key.

Just my 2¢.