| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
| Array formula returning wrong results | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
