Average Multiple Table Columns if between Two Dates

[jessebh2003]

I have been racking my brain and searching the internet for a solution to no avail. Hopefully, someone may know an answer.

I'm trying to calculate the average of nine columns but only if the value falls within a date range. The below table is abbreviated for demonstration purposes as the actual table has almost 30,000 rows.

EvalResults.xlsx
	E		G	H	I	J	K	L	M	N	O
1	SubmissionDate		TheContentWasRelevantToMyWork	ThisEventProvidedMeWithNewInform	IIntendToUseWhatILearnedFromThis	facultyknowledgeable	facultyeffective	facultyresponsive	facultyorganized	OverallTheConferenceWasFreeOfBia	TheLearningEnvironmentWasConduci
2	1/1/2021 0:48
3	1/1/2021 0:48				4				4
4	1/1/2021 0:48		4								4
5	1/1/2021 6:42
6	1/1/2021 6:42				5				4
7	1/1/2021 6:42		5								4
8	1/1/2021 9:48
9	1/1/2021 9:48				4				4
10	1/1/2021 9:48		4								4
Data

Here's what I've tried:

For reference - Year is a drop down in E1, and Jan is the month in E2 with Feb in G2, Mar in I2, etc.

1. Using AVERAGEIFS and CHOOSE - Populates #Value to a lot of cells

=AVERAGEIFS(CHOOSE({1,2,3,4,5,6,7,8,9},Table13[TheContentWasRelevantToMyWork],Table13[ThisEventProvidedMeWithNewInform],Table13[IIntendToUseWhatILearnedFromThis],Table13[facultyknowledgeable],Table13[facultyeffective],Table13[facultyresponsive],Table13[facultyorganized],Table13[OverallTheConferenceWasFreeOfBia],Table13[TheLearningEnvironmentWasConduci]),Table13[SubmissionDate],">="&DATE(Year,Jan,1),Table13[SubmissionDate],"<="&DATE(Year,Dec,31))

2. Using a Name Range - Returns a 0 value (ROL12CME references able13[TheContentWasRelevantToMyWork], Table13[ThisEventProvidedMeWithNewInform], Table13[IIntendToUseWhatILearnedFromThis], Table13[facultyknowledgeable], Table13[facultyeffective], Table13[facultyresponsive], Table13[facultyorganized], Table13[OverallTheConferenceWasFreeOfBia], Table13[TheLearningEnvironmentWasConduci])

=IFERROR(AVERAGEIFS(ROL12CME,Table13[SubmissionDate],">="&DATE(Year,Jan,1),Table13[SubmissionDate],"<="&DATE(Year,Dec,31)),0)

3. Using SUM, INDEX and FREQUENCY with and without a Name Range - Both returns an alert, "You've entered too few arguments for this function."

=IFS((SUM(Table13[TheContentWasRelevantToMyWork],Table13[ThisEventProvidedMeWithNewInform],Table13[IIntendToUseWhatILearnedFromThis],Table13[facultyknowledgeable],Table13[facultyeffective],Table13[facultyresponsive],Table13[facultyorganized],Table13[OverallTheConferenceWasFreeOfBia],Table13[TheLearningEnvironmentWasConduci])/INDEX(FREQUENCY((Table13[TheContentWasRelevantToMyWork],Table13[ThisEventProvidedMeWithNewInform],Table13[IIntendToUseWhatILearnedFromThis],Table13[facultyknowledgeable],Table13[facultyeffective],Table13[facultyresponsive],Table13[facultyorganized],Table13[OverallTheConferenceWasFreeOfBia],Table13[TheLearningEnvironmentWasConduci]),0),2)),Table13[SubmissionDate],">="&DATE(Year,Jan,1),Table13[SubmissionDate],"<="&DATE(Year,Dec,31)

=IFS((SUM(ROL12CME)/INDEX(FREQUENCY((ROL12CME),0),2)),Table13[SubmissionDate],">="&DATE(Year,Jan,1),Table13[SubmissionDate],"<="&DATE(Year,Dec,31)

Appreciate any help, ideas or suggestions. Thanks!

 

[jessebh2003]

Do you have any text values in the table, or formulae that return ""

Ah! Yes, there are some N/A values that are not counted towards the average. Apologies for not mentioning that.

 

[Fluff]

Ok try

=SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]),(ISNUMBER(Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]))*(Table13[SubmissionDate]>=DATE(2021,1,1))*(Table13[SubmissionDate]<DATE(2022,1,1)))/SUMPRODUCT((ISNUMBER(Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]))*(Table13[SubmissionDate]>=DATE(2021,1,1))*(Table13[SubmissionDate]<DATE(2022,1,1)))

 

[jessebh2003]

Ok try

=SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]),(ISNUMBER(Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]))*(Table13[SubmissionDate]>=DATE(2021,1,1))*(Table13[SubmissionDate]<DATE(2022,1,1)))/SUMPRODUCT((ISNUMBER(Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]))*(Table13[SubmissionDate]>=DATE(2021,1,1))*(Table13[SubmissionDate]<DATE(2022,1,1)))

Thanks, Fluff! That did the trick. Appreciate your help.

 

[Fluff]

Glad we could help & thanks for the feedback.