Date difference Average

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

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

"Kashyap" wrote:

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

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

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

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

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

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

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"")),"")


--
Biff
Microsoft Excel MVP


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