Hi,
I would like to get some average figures in the attached sheet.
1. Average count of container per month
2. Average number of items per container for the whole period
is there a quick formula that can give the result or will that be easier through pivot tables
Thanks
To return counts of items, containers and months use this in E2, copied across to G2:
=SUMPRODUCT(1/COUNTIF(A:A,A:A&""))-2
then your averages per month are easy!!
Last edited by Glenn Kennedy; 05-24-2017 at 03:27 AM.
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
BtW, if you use the ACTUAL cell ranges, instead of the whole columns, remove the -2. If you use an extended range (A2:A100, replace the -2 with a -1. The - bits are needed to remover blank cells and the header row from calculations, where included.
In "E2"Formula:
Please Login or Register to view this content.
In above formula "E1" is criteria : 01/05/2016
In "F2"Formula:Please Login or Register to view this content.
In above formula "F1" is criteria : A
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Thanks Glen.
Just one correction it was items per container. I have amended the formula in E4.
I manually checked the outcome. Containers per month is correct but the items per container is showing some variance.
As per manually checking the items per container is 1.75 but the formula is giving 1.5
In this sample dataset its not too much but I am dealing with 40000 rows where that figure would be significant.
Thanks again
You didn't say/I didn't spot that the same item could be in more than one container.
So item/container count:
=SUMPRODUCT(1/(COUNTIFS(A$2:A$16, A$2:A$16&"", B$2:B$16, B$2:B$16&"")))
Container count:
=SUMPRODUCT(1/COUNTIF(B2:B16,B2:B16&""))
Month count:
=SUMPRODUCT(1/COUNTIF(C2:C16,C2:C16&""))
See sheet.
Awesome it all works well. cheers mate
You're welcome and thanks for the feedback !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks