# Date difference Average

#### kashyap

##### Board Regular
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

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### silkfire

##### Active Member
=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

##### Active Member
Nvm I calculated it wrong, sorry........

#### Domenic

##### MrExcel MVP
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

##### MrExcel MVP
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

##### Active Member
Thanks jonmo!

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

#### kashyap

##### Board Regular
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

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

#### kashyap

##### Board Regular
Oh.. sorry... I totally over looked that.. Its working perfect

#### Domenic

##### MrExcel MVP
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...

1,101,866
Messages
5,483,416
Members
407,395
Latest member
Sakshine

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...