Date difference Average

[Kashyap]

I have 2 column as below and I want to calculate the average number of days
ABC occurs.. In this case it should be 3.33

Col A Col B
01-Apr ABC
01-Apr DBA
01-Apr ABC
03-Apr GRT
05-Apr HTA
05-Apr JYU
08-Apr ABC
08-Apr GRT
08-Apr HTA
10-Apr JYU
11-Apr ABC

 

[Mike H]

Hi,

Please explain why the answer is 3.33. I think 4 of 11 in your list are ABC
so 4/11 =36.36% or with this formula

=COUNTIF(B1:B11,"abc")/COUNTA(B1:B11)

You don't explain the relevance of the dates.

Mike

Mike

 

[T. Valko]

it should be 3.33

Can you explain how you arrive at that result?

 

[Kashyap]

date when ABC occurs is

01-Apr
01-Apr 0
08-Apr 7
11-Apr 3

3.33

 

[T. Valko]

Ok, I get it.

This is pretty complicated to try in a single formula so I would use a
helper column.

Assume your data is in the range A2:B12.

Enter this formula in *C3* and copy down to C12:

=IF(B3<>"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2))

Then to get the average:

=AVERAGE(C3:C12)

 

[T. Valko]

Enter this formula in *C3* and copy down to C12:

=IF(B3<>"abc","",A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2))

I found a bug in that formula. Use this one:

=IF(B3="abc",IF(COUNTIF(B$2:B3,"abc")>1,A3-LOOKUP(2,1/(B$2:B2="abc"),A$2:A2),""),"")

While we're at it:

Then to get the average:
=AVERAGE(C3:C12)

Let's change that formula to:

=IF(COUNTIF(C3:C12,">0"),AVERAGE(C3:C12),0)

 

[Kashyap]

Thank you Valko.. This seems to be working fine. But how to implement this
was all the names and not only abc? I have a list of unique name in ColE

 

[T. Valko]

I don't think this can be done without using a helper column. If you can
sort your data by column B and then by column A so that all the same codes
are grouped together then you could use just a single helper column.
Otherwise, you'd need a helper column for each different code which may not
be practical.

For example, sort the data by column B (ascending order) then by column A
(ascending order) so that the data ends up like this:

01-Apr...ABC
01-Apr...ABC
08-Apr...ABC
11-Apr...ABC
01-Apr...DBA
03-Apr...GRT
08-Apr...GRT
05-Apr...HTA
08-Apr...HTA
05-Apr...JYU
10-Apr...JYU

Then, in column C starting in cell C3 you can enter this formula:

=IF(B3=B2,A3-A2,"")

This will return the differences between dates:

01-Apr...ABC....
01-Apr...ABC...0
08-Apr...ABC...7
11-Apr...ABC...3
01-Apr...DBA....
03-Apr...GRT....
08-Apr...GRT...5
05-Apr...HTA....
08-Apr...HTA...3
05-Apr...JYU....
10-Apr...JYU...5

If you have a list of the unique codes in column E:

E2 = ABC
E3 = DBA
E4 = GRT
E5 = HTA
E6 = JYU

To get the averages enter this formula in F2 and copy down to F6:

=IF(SUMIF(B$2:B$12,E2,C$2:C$12),SUMIF(B$2:B$12,E2,C$2:C$12)/SUMPRODUCT(--(B$2:B$12=E2),--(C$2:C$12<>"")),"")