Formula for average of all networkdays values between 2 columns

[PatrickSchmidt]

Hi,
I have this formula to find the difference between dates minus the weekends.
=IF(ISNUMBER(N2),MAX(0,NETWORKDAYS(M2,N2,)-1),"")
I have been putting it in an empty cell and filling down. Then in another cell I average that column.
My question is can I write one formula in one cell that will give me the same result?

Thank you

 

[Mike LH]

Hi,

Your formula is confusing me, why are you subtracting 1 from the result? Anyway, the way I think it should be done is with this ARRAY formula, see below for how to enter it. Extend the ranges as required but keep them all the same size'


=(AVERAGE(IF(N2:N12<>"",INT((WEEKDAY(M2:M12-{1,2,3,4,5},2)+N2:N12-M2:M12)/7)))*5)

or with your -1

=(AVERAGE(IF(N2:N12<>"",INT((WEEKDAY(M2:M12-{1,2,3,4,5},2)+N2:N12-M2:M12)/7)))*5)-1

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[PatrickSchmidt]

Hi Mike thanks for the reply.
It's not working.
I pasted your formula into Z2 and it gives me 2 no matter what I change the dates in columns M and N to.

 

[Mike LH]

Hi Mike thanks for the reply.
It's not working.
I pasted your formula into Z2 and it gives me 2 no matter what I change the dates in columns M and N to.

Did you read this bit of my post and ARRAY enter the formula?


This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[PatrickSchmidt]

Oh sorry I didn't understand that part.

It works, thanks a lot!

Is there a way to apply that to entire columns? I tried taking out the numbers in the range references but it errored. The file has rows added daily and I'd like not to have to change the numbers everytime.


The reason I put the minus one is because if the dates are May 5th and May 6th it gives it 2 days but I want just one for the situation.

 

[Mike LH]

Hi,

ARRAY formula are slow and if you apply this to full columns you will see a noticeable deterioration in workbook speed. FWIW I wouldn't do this at all, I only wrote the formula as an exercise and because you wanted it. I would use a helper column and average the results of that. this does full columns, don't forget to ARRAY enter it


=(AVERAGE(IF(N:N<>"",(INT(WEEKDAY(M:M-{1,2,3,4,5},2)+N:N-M:M)/7)))*5)-1