Average with conditions and a conversion

[djmsenior]

I'm trying to write a formula that finds the average from a table with 3 sets of criteria:
Agent = X
Activity = Y
Day = Z
Average duration for those conditions.
I've got the formula but 1 part of it requires an additional column (F) that converts the activity start date/time into the day of the month.

View attachment 94846


I've tried using DAY() on the Activity Start Date/Time column to eliminate the need for the extra conversion column, but just get an error.

Any help gratefully received.

 

[djmsenior]

For some reason the original image didn't pull through...hopefully this will.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post your working formula.

 

[djmsenior]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post your working formula.

O365 on Windows 10.

The working formula is in the screenshot.

Thanks for the heads up!

 

[Fluff]

The working formula is in the screenshot.

Yes but that means I have to type it all out again. Hence I asked you to post the actual formula.

 

[djmsenior]

Yes but that means I have to type it all out again. Hence I asked you to post the actual formula.

=AVERAGEIFS(Data!$E$2:$E$11,Data!$A$2:$A$11,$J$3,Data!$B$2:$B$11,$J4,Data!$F$2:$F$11,K$2)

As an FYI I have used something similar to this before in SUMPRODUCT MONTH()

=SUMPRODUCT(1*(MONTH(Data!G2:G190)=A3),1*((Data!S2:S190)=B3))

So maybe I just need to change the AVERAGEIFS to a SUMPRODUCT formula?

 

[Fluff]

Thanks for that, how about

=AVERAGE(FILTER(Data!$E$2:$E$11,(Data!$A$2:$A$11=$J$3)*(Data!$B$2:$B$11=$J4)*(DAY(Data!$C$2:$C$11)=K$2)))

 

[djmsenior]

Thanks for that, how about

=AVERAGE(FILTER(Data!$E$2:$E$11,(Data!$A$2:$A$11=$J$3)*(Data!$B$2:$B$11=$J4)*(DAY(Data!$C$2:$C$11)=K$2)))

Many thanks for that Fluff! Works a treat.

 

[Fluff]

You're welcome & thanks for the feedback.