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

 

[silkfire]

=countif(b1:b11,"abc")/counta(b1:b11)

And the result is 0.3636...infinity to be exact.


EDIT: Who edits my formulas from Upper case to lower case?

 

[silkfire]

Nvm I calculated it wrong, sorry........

 

[Domenic]

Assuming that A2:B12 contains the data, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(SMALL(IF(B2:B12="ABC",A2:A12),ROW(INDIRECT("2:"&COUNTIF(B2:B12,"ABC"))))-SMALL(IF(B2:B12="ABC",A2:A12),ROW(INDIRECT("1:"&COUNTIF(B2:B12,"ABC")-1))))

 

[Jonmo1]

EDIT: Who edits my formulas from Upper case to lower case?

The board software does that. It's trying to prevent someone from writing in all CAPS, which is against the forum policy. It's easy to get around, just write something else BEFORE then formula, like

Try this

FORMULA HERE..

 

[silkfire]

Thanks jonmo!

Domenic how did you make up such an advanced formula so quickly?

 

[kashyap]

Hi Domenic.. This is the output I got..

01-Apr ABC 0
01-Apr DBA #NUM!
01-Apr ABC 2
03-Apr GRT #NUM!
05-Apr HTA #NUM!
05-Apr JYU #NUM!
08-Apr ABC 0
08-Apr GRT #REF!
08-Apr HTA #REF!
10-Apr JYU #REF!
11-Apr ABC #REF!

 

[silkfire]

Don't ENTER, use Ctrl+Shift+Enter, it's a so called "array formula".

 

[kashyap]

Oh.. sorry... I totally over looked that.. Its working perfect

 

[Domenic]

Domenic how did you make up such an advanced formula so quickly?

Over time, one tends to pick-up a thing or two after frequenting this Board...