Array of dates

[snjpverma]

Hi I would like to have a formula wherein the average of Networkdays are calculated without helper column.

In the attached sheet, I want the output in column G but without the helper column C.

 

[Haz]

See the attached file.

 

[snjpverma]

Wow, that works absolutely fine. Thanks for the help.

However, I was wondering why this can't be done with "Averageif" or "aggregate" function. I was trying to get the output on my own using either of these functions last night.
As a part of learning, I would like to know if there is another methods of doing this.
Thanks for the help Haz.

 

[NARAYANK991]

Hi ,

You can use the following array formula , to be entered using CTRL SHIFT ENTER :

=AVERAGE(IF($A$2:$A$13 = F2, NETWORKDAYS($B$2:$B$13+0, TODAY()-1)))

Narayan

 

[GraH - Guido]

Wow, that works absolutely fine. Thanks for the help.

However, I was wondering why this can't be done with "Averageif" or "aggregate" function. I was trying to get the output on my own using either of these functions last night.
As a part of learning, I would like to know if there is another methods of doing this.
Thanks for the help Haz.

Aggregate function with the option average cannot handle array calculations in the array argument. For more details look in the Excel function inner help.
Averageif's arguments do not accept array arguments, only ranges.