Array formula using NETWORKDAYS

[nicostick]

I'm having difficulty with an array formula using dates. What I'd like to do is, for a range of dates (in columns A and K), calculate the average of the NETWORKDAYS between them.

The formula I have now is:
{=AVERAGE(NETWORKDAYS(A9:A16,K9:K16))}
which returns a #VALUE! error.

Is it possible to use NETWORKDAYS in an array formula? It's very important that I calculate these numbers using business days.

 

[Mark W.]

NETWORKDAYS only accepts individual date values as arguments.

 

[just_jon]

No way of creating a {possibly hidden} column to derive the NETWORDAYS on a by-row basis, then average that data? Could even be on another sheet.

 

[nicostick]

I could go this route, but I'd like to keep this spreadsheet as lean as possible (I'm trying to tidy up a spreadsheet that was left by a prevous employee).

Shouldn't an array formula be possible?

 

[just_jon]

I can't think of anything offhand that would produce the desired result, given that NETWORKDAYS is not an option. (In terms of array formulas.)

Alternatively, if it is a "visually clean" look you are after in the workbook, you could perform the calculation using a UDF, I suppose.

 

[nicostick]

UDF? I'm not familiar with that...

 

[just_jon]

User Defined Function; basically a macro that is callable from a cell like --

=FunctionNameHere(ArgumentListHere)

Where FunctionName refers to a macro FUNCTION, and the argument list are any required variables passed to the function.

 

[nicostick]

Sorry, I didn't notice Mark's post re: NETWORKDAYS.

In this case, just_jon, I'm going to run with what you suggested first -- probably a hidden sheet with the formulas contained there.

Thanks very much.