Excel FORMULA: count average of duplicates!!!

S

samuele2723

New Member
Joined
Jun 26, 2014
Messages
42
Hello folks,
I’m struggling with a formula.

Here is the situation:

I have a list of entries with a date and I want to calculate for each date how many entries I have.
Then, at final, know the average of entries that I have per day.

At first looks simple but I am struggling so bad..

Example:

Entry 1 - 12/10/2014
Entry 2 - 12/10/2014
Entry 3 - 13/10/2014
Entry 4 - 13/10/2014
Entry 5 - 14/10/2014

As a result, I want to see in a cell: 1,66666 that is the average of entries per same day.

Thanks in advance!
Sam
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
samuele2723 said:
thanks! works great! but only if i put a number..if i type 13/10 it does not read the "date" in the formula and i receive 1 as output all the time
Click to expand...

A date is a number. Typing 13/10 would probably give a text value. Are you going mix true dates with text dates or they all will be text dates?
 
Upvote 0
Aladin Akyurek said:
Assuming text dates...

Control+shift+enter:
Rich (BB code): 
=COUNTIF(B2:B6,"?*")/SUM(IF(FREQUENCY(IF(B2:B6<>"",
  MATCH(B2:B6,B2:B6,0)),ROW(B2:B6)-ROW(B2)+1),1))
Click to expand...

Hello, thanks for your reply. I tried to use the code but i get back an error. I noticed that the first countif counts if i type text but still not if i type 13/10 or CTRL+;. Was it working on your sheet? Sam
 
Upvote 0
samuele2723 said:
Hello, thanks for your reply. I tried to use the code but i get back an error. I noticed that the first countif counts if i type text but still not if i type 13/10 or CTRL+;. Was it working on your sheet? Sam
Click to expand...

You are asking for trouble if B2:B6 mixes true dates and text dates.

If you have only true dates, invoke the formula:

=COUNT(B2:B6)/SUM(IF(FREQUENCY(B2:B6,B2:B6),1))

If you have only text dates, invoke the formula:

=COUNTIF(B2:B6,"?*")/SUM(IF(FREQUENCY(IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0)),ROW(B2:B6)-ROW(B2)+1),1))

If

=ISNUMBER(B2) --> TRUE (meaning a true date)

=ISNUMBER(B3) --> FALSE (meaning a text date)

while they are supposed to be equal, i.e.,

=B2=B3 --> TRUE

you will not get a correct result.
 
Upvote 0
Aladin Akyurek said:
You are asking for trouble if B2:B6 mixes true dates and text dates.

If you have only true dates, invoke the formula:

=COUNT(B2:B6)/SUM(IF(FREQUENCY(B2:B6,B2:B6),1))

If you have only text dates, invoke the formula:

=COUNTIF(B2:B6,"?*")/SUM(IF(FREQUENCY(IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0)),ROW(B2:B6)-ROW(B2)+1),1))

If

=ISNUMBER(B2) --> TRUE (meaning a true date)

=ISNUMBER(B3) --> FALSE (meaning a text date)

while they are supposed to be equal, i.e.,

=B2=B3 --> TRUE

you will not get a correct result.
Click to expand...

My dear, thanks for your clarification. Now works like a charm! Have a nice day!! (topic can be put to solved). Regards Sam
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Zero all cells before the next entry if greater then 120days
Replies
5
Views
129
bobaol
B
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
193
etaf
etaf
S
  • Solved
Average in excel
Replies
3
Views
55
etaf
etaf
jski21
  • Solved
Find last entry in column and sum all prior rows
Replies
3
Views
119
Fluff
Fluff
J
  • Solved
Excel Formula To Average all numbers in one month from list of dates
Replies
2
Views
467
Dave Patton
D

Forum statistics

Threads
1,214,659
Messages
6,120,783
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top