jollygreengiant
New Member
- Joined
- Dec 7, 2015
- Messages
- 6
I have been going round in circles with this for a while now....
I have a table as follows;
A1 ProjectDate ClientName ProjectDuration
A2 22-Sep-15 Client 1 1:56:20
A3 22-Sep-15 Client 2 8:33:21
A4 22-Sep-15 Client 3 2:45:55
A5 22-Sep-15 Client 4 16:22:01
A6 22-Sep-15 Client 5 1:00:00
A7 22-Sep-15 Client 6 23:43:21
A8 30-Apr-15 Client 1 6:07:54
A9 30-Apr-15 Client 2 8:33:21
A10 30-Apr-15 Client 3 0:33:21
A11 12-Feb-15 Client 6 18:00:21
A12 12-Feb-15 Client 1 6:43:05
A13 12-Feb-15 Client 2 9:32:02
A14 12-Feb-15 Client 3 10:22:32
A15 12-Feb-15 Client 4 1:22:45
A16 12-Feb-15 Client 5 10:44:02
A17 01-Jan-15 Client 6 4:30:00
A18 01-Jan-15 Client 1 12:10:02
And I want to know the average of the quickest n projects on a given date. The data columns are named ProjectDate, ClientName and ProjectDuration to avoid complicated range names in the formulas.
Cell E1 contains the date that I am running the formula on. So say E1=22-Sep-15 for this example and I want to find the average time for the fastest 3 projects started on that date.
My logic is;
Step 1 get a list of all the projects on 22-Sep-15
Step 2 add together only the fastest 3 times
Step 3 divide the result by 3 to get the average time
=SUMPRODUCT(SMALL((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3
or
{=SUM(SMALL((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3}
However the result is always 0
Interestingly if I flip the requirements of the formula to "find the average of the SLOWEST 3 projects on 22-Sep-15" using the following formula;
=SUMPRODUCT(LARGE((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3
It works absolutely fine giving the result 16:12:54.
It may be possible to do the same using AVERAGEIF. AVERAGEIF(ProjectDate,E1,ProjectDuration) will return the average project time of every project on 22-Sep-15 but I have not been able to make this work with the extra condition of the fastest 3 projects.
Any help would hugely appreciated. I have been trawling the internet for a few days now trying to figure out a solution.
Thanks.
I have a table as follows;
A1 ProjectDate ClientName ProjectDuration
A2 22-Sep-15 Client 1 1:56:20
A3 22-Sep-15 Client 2 8:33:21
A4 22-Sep-15 Client 3 2:45:55
A5 22-Sep-15 Client 4 16:22:01
A6 22-Sep-15 Client 5 1:00:00
A7 22-Sep-15 Client 6 23:43:21
A8 30-Apr-15 Client 1 6:07:54
A9 30-Apr-15 Client 2 8:33:21
A10 30-Apr-15 Client 3 0:33:21
A11 12-Feb-15 Client 6 18:00:21
A12 12-Feb-15 Client 1 6:43:05
A13 12-Feb-15 Client 2 9:32:02
A14 12-Feb-15 Client 3 10:22:32
A15 12-Feb-15 Client 4 1:22:45
A16 12-Feb-15 Client 5 10:44:02
A17 01-Jan-15 Client 6 4:30:00
A18 01-Jan-15 Client 1 12:10:02
And I want to know the average of the quickest n projects on a given date. The data columns are named ProjectDate, ClientName and ProjectDuration to avoid complicated range names in the formulas.
Cell E1 contains the date that I am running the formula on. So say E1=22-Sep-15 for this example and I want to find the average time for the fastest 3 projects started on that date.
My logic is;
Step 1 get a list of all the projects on 22-Sep-15
Step 2 add together only the fastest 3 times
Step 3 divide the result by 3 to get the average time
=SUMPRODUCT(SMALL((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3
or
{=SUM(SMALL((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3}
However the result is always 0
Interestingly if I flip the requirements of the formula to "find the average of the SLOWEST 3 projects on 22-Sep-15" using the following formula;
=SUMPRODUCT(LARGE((ProjectDate=E1)*(ProjectDuration), {1,2,3}))/3
It works absolutely fine giving the result 16:12:54.
It may be possible to do the same using AVERAGEIF. AVERAGEIF(ProjectDate,E1,ProjectDuration) will return the average project time of every project on 22-Sep-15 but I have not been able to make this work with the extra condition of the fastest 3 projects.
Any help would hugely appreciated. I have been trawling the internet for a few days now trying to figure out a solution.
Thanks.
