Can someone tell me what the best way to average entries per day, by date is? I just want to know how many entries per day on average.
Also, If it's not too much more, i may need the ability to to average entries per day by person. So if the dates are in A column, and in B column is the person inputting the data, can i average per day by user also?
Thanks a lot!
See Sample data below.
Last edited by Deeno04; 12-09-2021 at 03:38 PM.
There certainly must be a way to accomplish what you are after.
Please follow instructions on yellow banner above to upload your sample workbook.
Sorry about that! I've attached a sample of data. Thanks for looking!
Last edited by Deeno04; 12-09-2021 at 09:09 AM.
There are no person IDs in the sheet. This counts the number per day:
=SUMPRODUCT(--(INT($B$2:$B$37)=E2))
see the sheet.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
=COUNT(B2:B37)/SUM((FREQUENCY(INT(B2:B37),INT(B2:B37))>0)+0)
try above formula
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Ignore my post! I totally mis-read what you wanted...
I added more info in the example data. I still cant get it to calculate correctly. I thought Samba_ravi's formula worked, but once i expand it to calculate the whole column, it doesn't work.
See below:
AliGW on MS365 Insider (Windows) 64 bit
entries on 12-6 =SUMPRODUCT(--(INT($B$2:$B$37)=DATE(2021,12,6))*($B$2:$B$37<>"")) entries on 12-7 =SUMPRODUCT(--(INT($B$2:$B$37)=DATE(2021,12,7))*($B$2:$B$37<>"")) entries on 12-8 =SUMPRODUCT(--(INT($B$2:$B$37)=DATE(2021,12,8))*($B$2:$B$37<>"")) daily average entries =AVERAGE(G7:G9) entries for 904 on 12-6 =SUMPRODUCT(--(INT($B$2:$B$37)=DATE(2021,12,6))*($D$2:$D$37="904")) entires for 904 on 12-7 =SUMPRODUCT(--(INT($B$2:$B$37)=DATE(2021,12,7))*($D$2:$D$37="904")) entires for 904 on 12-8 =SUMPRODUCT(--(INT($B$2:$B$37)=DATE(2021,12,8))*($D$2:$D$37="904")) daily average for 904 =AVERAGE(G14:G16)
Sheet: Sheet1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
samba's formula works perfectly!!
=COUNT(B2:B37)/SUM((FREQUENCY(INT(B2:B37),INT(B2:B37))>0)+0)
Post a file showing us where it DOES NOT work.
There are two ways of assessing the average no of 904's calls. He was there only on two out of the 3 days. If you want to take into account only the days PRESENT (2) use:
=COUNTIF(D2:D37,904)/COUNT(UNIQUE(FILTER(INT(B2:B37),D2:D37=904)))
or if you want to count all the days in the dataset (3), use:
=COUNTIF(D2:D37,904)/COUNT(UNIQUE(INT(B2:B37)))
Thank you all for helping me with this. I've added examples to the sheet where the formula isn't working correctly. These data entries will grow to several thousand over the next few years, so I'm trying to automate as much as possible. I have a front end sheet with graphs and numbers, and this data is on sheet 2. Sheet 2 will keep growing. Thank you all again!
I cut your sample down to make it easier to follow. The problem was several-fold.
When you added more data, you did not adjust the formula to cover the range of the data. Data went to row 61... the formula to 37.
The next problem was that the inclusion of the blank cells threw everything off. They need to be filtered out. I have done that in the formulae in the sheet. They give the SAME answer when the EXACT range or an EXTENDED range is used.
You might be better to use Named Ranges to auto-adjust the formulae to the exact length of your dataset. Do you know how to do that?
For now, see the sheet.
I don't know how to do that.Originally Posted by Glenn Kennedy
So I used this formula you just gave me, and it gives me an average of 15, when it should be 20. Is it because i expanded the range wrong? I'm sorry i feel like im making this difficult for you guys. I wish i knew half of what you guys know about Excel.
=COUNT(Sheet2!C2:C50000)/SUM((FREQUENCY(INT(Sheet2!C2:C50000),INT(Sheet2!C2:C50000))>0)+0)
Read Post 11. Look at the formulae IN THE SHEET.
I keep forgetting to tell you... Some of the values in column D are TEXT that look like numbers. To convert them ALL to numbers, select column D.
Data/Data Tools/Text to Columns/Finish
job done.
I have now added two Named ranges to the sheet. Date looks like this:
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(1E+100,Sheet1!$B:$B))
and Name is simailr, referring to column D. CTRL-F3 to view/edit them.
The two formulae now simplify to:
=COUNT(Date)/SUM((FREQUENCY(INT(Date),INT(Date))>0)+0)
and
=COUNTIF(Call,904)/COUNT(UNIQUE(INT(Date)))
They will automatically adapt to the addition of new data and will only calculate the number of rows that are non-blank
Glenn, you are the man!!! Thank you so much for helping me with this! Thank you to all the others who were helpful as well!
You're welcome & thanks for the feedback.
Hey Glenn,
I'm not sure if this is ok to ask a question in an old thread, or if i should create a new thread, but the question is specifically about this thread and this formula you helped me with last December. Is there a way to use this same formula, and only average the last 7 days? =COUNTIF(Call,904)/COUNT(UNIQUE(INT(Date))) adding something like this in? ">="&TODAY()-7
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks