ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Array Formula - NetworkDays (https://www.excelbanter.com/excel-discussion-misc-queries/115738-array-formula-networkdays.html)

Paul Martin

Array Formula - NetworkDays
 
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

Array Formula - NetworkDays
 
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" wrote in message
ps.com...
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




Paul Martin

Array Formula - NetworkDays
 
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 wrote:
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" wrote in message
ps.com...
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

Array Formula - NetworkDays
 
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
ps.com...
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





Paul Martin

Array Formula - NetworkDays
 
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 wrote:
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
ps.com...
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

Array Formula - NetworkDays
 
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)

"Paul Martin" wrote in message
ups.com...
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 wrote:
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
ps.com...
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







All times are GMT +1. The time now is 08:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com