Date Calculation

[snjpverma]

Hi Experts,

My data is as below. is the output in Column G possible without helper column C?

	A	B	C	D	E	F	G
1	Name	Dates	Difference				Output average
2	John	4/29/2018	4			John	22.5
3	Tom	4/16/2018	14			Tom	22.25
4	Von	4/13/2018	15			Von	27
5	John	3/13/2018	38
6	Tom	4/4/2018	22
7	Von	3/18/2018	34
8	John	3/18/2018	34
9	Tom	3/29/2018	26
10	Von	4/5/2018	21
11	John	4/15/2018	14
12	Tom	3/28/2018	27
13	Von	3/13/2018	38

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:73px;"><col style="width:73px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:104px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =NETWORKDAYS(B2,TODAY()-1) G2 =AVERAGEIF($A$2:$A$13,F2,$C$2:$C$13) G3 =AVERAGEIF($A$2:$A$13,F3,$C$2:$C$13) G4 =AVERAGEIF($A$2:$A$13,F4,$C$2:$C$13) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[snjpverma]

Just to highlight that the formula used column C has been mentioned at the bottom in Post #1 . It is a networkdays function which I use to find the difference from yesterday to the Date in Column B.

 

[snjpverma]

any formula for this one? The only thing I want is: get the average of networkdays without the helper column C.
Column C is the networkdays from Date in column B to Yesterday.

This is basically what I want.

=Averageif(A$2:A13,F2,Networkdays(B2:B13),Yesterday)

But the networkdays doesn't accept the array function in this manner.

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter, and copy down:

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

 

[snjpverma]

wow that's brilliant Aladin. I was trying this for so long.
This forum is full of genius people. Whenever I come here, I learn something new.