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!

 

[SanjayGMusafir]

Just a food for thought - By any chance have you thought about or tried

SUMIFS/COUNTIFS - That shall be the average

 

[jessebh2003]

Just a food for thought - By any chance have you thought about or tried

SUMIFS/COUNTIFS - That shall be the average

I haven't. Can you say more?

 

[SanjayGMusafir]

I haven't. Can you say more?

See you can SUMIFS a range of adjacent columns based on Criteria(s)

COUNTIFS not sure you have check individual columns or you can count for a range of adjacent columns. But it can be calculated.

Either ways at most two helper cells and average can be found, if populating all above gets messy in a cell.

 

[jessebh2003]

See you can SUMIFS a range of adjacent columns based on Criteria(s)

COUNTIFS not sure you have check individual columns or you can count for a range of adjacent columns. But it can be calculated.

Either ways at most two helper cells and average can be found, if populating all above gets messy in a cell.

Maybe I'm doing something wrong. I'm getting the alert about too few arguments.

=SUMIFS(Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]],">="&DATE(Year,Jan,1),Table13[SubmissionDate],"<="&DATE(Year,Dec,31))/COUNTIFS(Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]],Table13[SubmissionDate],">="&DATE(Year,Jan,1),Table13[SubmissionDate],"<="&DATE(Year,Dec,31))

 

[Fluff]

You cannot use any of the xxxIFS function like that, all the ranges must be the same size.
Try

=SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]])*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate],"<="&DATE(Year+1,Jan,1)))/SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]<>"")*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate],"<="&DATE(Year+1,Jan,1)))

 

[jessebh2003]

You cannot use any of the xxxIFS function like that, all the ranges must be the same size.
Try

=SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]])*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate],"<="&DATE(Year+1,Jan,1)))/SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]<>"")*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate],"<="&DATE(Year+1,Jan,1)))

Hi Fluff,
Thanks. That returned the alert, "There's a problem with this formula. Not trying to type a formula?..."

 

[Fluff]

Oops, it should be

=SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]])*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate]<DATE(Year+1,Jan,1)))/SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]<>"")*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate]<DATE(Year+1,Jan,1)))

 

[jessebh2003]

Oops, it should be

=SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]])*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate]<DATE(Year+1,Jan,1)))/SUMPRODUCT((Table13[[TheContentWasRelevantToMyWork]:[TheLearningEnvironmentWasConduci]]<>"")*(Table13[SubmissionDate]>=DATE(Year,Jan,1))*(Table13[SubmissionDate]<DATE(Year+1,Jan,1)))

That's giving me a #VALUE! result.

 

[Fluff]

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