Array Formula - NetworkDays

[Paul Martin]

Hi all

I am attempting to use an array formula which I can't get to work.

The column headings are consecutive days of the year and the row
headings are rollout dates.
The array formula I am attempting to use is:

=IF(B$1:GC$1<=$A2:$A793, 0, (NETWORKDAYS($A2:$A793, B$1:GC$1)-1))

I am getting #VALUE! errors where the calendar dates are greater than
the rollout dates.

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Bob Phillips]

Paul,

I don't think you can do that.

What exactly is the objective, there may be an alternative? We know B1:GC1
is half a year dates, what is A2:A793, and what is being calculated in
NETWORKDAYS?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Paul Martin]

Hi Bob

I have since read that NETWORKDAYS doesn't work with array formulas.
The calculation is the number of days since an event (deployment)
occurred. So you look up the column headings for a given date, you
look at the row heading for the deployment date, and you can see the
number of days since deployment.

Paul

 

[Bob Phillips]

Paul,

isn't it just

=SUMPRODUCT(--(TODAY()-A2:A793))

i.e. no need for the dates in B1...

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Paul Martin]

Hi Bob

I don't understand the "--" in your suggested formula.

The worksheet displays a 'map' of the time period, so the dates in row
1 are required.

Paul

 

[Bob Phillips]

Don't understand, a map in what sense? Can you layout some data and expected
results?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)