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" wrote:

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)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Kashyap" wrote in message
...
date when ABC occurs is

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

3.33
---


"T. Valko" wrote:

it should be 3.33

Can you explain how you arrive at that result?


--
Biff
Microsoft Excel MVP


"Kashyap" wrote in message
...
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