Average of Two Columns Meeting a Specific Criterion

[JeffFinnan]

Below is a partial list of data. I have two separate nest types. I want to get the average the temperatures of the combined set for each number of days. For example the average temperature of the temperatures at 51 days, at 52 days, etc. That is easy for me set up for one set but not for the two sets.

Nest	Laid	Emerged	Temp	Inc Days			Nest	Laid	Emerged	Temp	Inc Days
GS001	5/3​	7/4​	80.1​	62​			GN001	5/4​	7/4​	80.1​	61​
GS002	5/8​	7/5​	80.5​	58​			GN002	5/8​	7/3​	80.3​	56​
GS003	5/9​	7/6​	80.5​	58​			GN003	5/8​	7/5​	80.5​	58​
GS004	5/13​	7/11​	80.9​	59​			GN004	5/10​	7/4​	80.4​	55​
GS005	5/13​	7/11​	80.9​	59​			GN005	5/14​	7/5​	80.9​	52​
GS006	5/15​	7/5​	80.9​	51​			GN006	5/15​	7/12​	81.1​	58​
GS007	5/17​	7/14​	81.3​	58​			GN007	5/15​	7/10​	81.1​	56​
GS008	5/18​	7/17​	81.4​	60​			GN008	5/17​	7/12​	81.2​	56​
GS009	5/19​	7/15​	81.5​	57​			GN009	5/18​	7/14​	81.4​	57​
GS010	5/20​	7/13​	81.4​	54​			GN010	5/19​	7/14​	81.5​	56​
GS011	5/20​	7/10​	81.4​	51​			GN011	5/20​	7/14​	81.5​	55​
GS012	5/21​	7/14​	81.7​	54​			GN012	5/20​	7/16​	81.5​	57​
GS013	5/21​	7/13​	81.6​	53​			GN013	5/24​	7/16​	81.9​	53​
GS014	5/22​	7/13​	81.7​	52​			GN014	5/25​	7/14​	82.0​	50​
GS015	5/22​	7/14​	81.8​	53​			GN015	5/25​	7/15​	82.0​	51​
GS016	5/23​	7/15​	81.9​	53​			GN016	5/25​	7/15​	82.0​	51​
GS017	5/23​	7/17​	81.8​	55​			GN017	5/25​	7/13​	81.9​	49​
GS018	5/23​	7/14​	81.8​	52​			GN018	5/26​	7/13​	81.9​	48​
GS019	5/24​	7/14​	81.9​	51​		nv	GN019	5/26​	#N/A​	#N/A​	#N/A​
GS020	5/24​	7/14​	81.9​	51​			GN020	5/27​	7/19​	82.0​	53​
GS021	5/25​	#N/A​	#N/A​	#N/A​			GN021	5/27​	7/15​	82.0​	49​
GS022	5/26​	7/19​	81.9​	54​			GN022	5/28​	7/21​	81.9​	54​
GS023	5/29​	7/18​	82.0​	50​			GN023	5/29​	7/18​	82.0​	50​
GS024	5/30​	7/20​	82.0​	51​			GN024	5/29​	7/25​	81.9​	57​
GS025	5/30​	7/15​	82.1​	46​			GN025	5/30​	7/19​	82.0​	50​
GS026	5/30​	7/18​	82.0​	49​			GN026	5/30​	7/20​	82.0​	51​
GS027	5/31​	7/24​	82.0​	54​			GN027	5/30​	7/25​	81.9​	56​
GS028	5/31​	7/21​	82.0​	51​			GN028	6/1​	7/19​	82.1​	48​
GS029	6/1​	#N/A​	#N/A​	#N/A​			GN029	6/1​	7/23​	82.0​	52​
GS030	6/1​	7/21​	82.1​	50​			GN030	6/1​	7/24​	82.1​	53​
GS031	6/1​	7/21​	82.1​	50​			GN031	6/2​	7/21​	82.1​	49​
GS032	6/2​	#N/A​	#N/A​	#N/A​			GN032	6/3​	7/25​	82.2​	52​
GS033	6/5​	7/24​	82.3​	49​			GN033	6/4​	7/24​	82.3​	50​
GS034	6/5​	7/27​	82.3​	52​			GN034	6/4​	7/23​	82.2​	49​
GS035	6/6​	7/26​	82.4​	50​			GN035	6/4​	7/25​	82.3​	51​
GS036	6/6​	7/29​	82.3​	53​			GN036	6/4​	7/24​	82.3​	50​
GS037	6/7​	7/30​	82.3​	53​			GN037	6/4​	7/25​	82.3​	51​
GS038	6/8​	7/31​	82.2​	53​			GN038	6/5​	7/26​	82.3​	51​
GS039	6/9​	7/31​	82.2​	52​			GN039	6/6​	7/25​	82.4​	49​
GS040	6/9​	7/29​	82.3​	50​			GN040	6/6​	7/26​	82.4​	50​
GS041	6/9​	7/27​	82.3​	48​			GN041	6/6​	7/25​	82.4​	49​
GS042	6/10​	7/31​	82.2​	51​			GN042	6/7​	8/2​	82.3​	56​

 

[alansidman]

If you have O365 you can use the GroupBy function

=GROUPBY(Table1[Inc Days],Table1[Temp],AVERAGE)

otherwise, you can groupby in power Query and apply the Average function within that functionality.

If you need to see this applied, then repost your data using XL2BB. Your current presentation does not allow for this as it presents as Text when copied and pasted. I will not spend any time rebuilding your spreadsheet manually when you can provide the information in a usable format.

 

[kvsrinivasamurthy]

Columns D and K are temp. Columns E and L are Inc Days.
R2= 51
ARRAY Formula in S2

=IFERROR((SUM(IFERROR(($E$3:$E$300=$R2)*($D$3:$D$300),0))+SUM(IFERROR(($L$3:$L$300=$R2)*($K$3:$K$300),0)))/(SUM(1*IFERROR($E$3:$E$300=$R2,0))+SUM(1*IFERROR($L$3:$L$300=$R2,0))),"")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[myall_blues]

How about:

=(SUMIFS(D2:D43,E2:E43,M2)+SUMIFS(J2:J43,K2:K43,M2))/(COUNTIFS(E2:E43,M2)+COUNTIFS(K2:K43,M2))

where M2 contains the day you’re seeking the average of (e.g. 51).

 

[JeffFinnan]

f you have O365 you can use the GroupBy function

=GROUPBY(Table1[Inc Days],Table1[Temp],AVERAGE)

otherwise, you can groupby in power Query and apply the Average function within that functionality.

If you need to see this applied, then repost your data using XL2BB. Your current presentation does not allow for this as it presents as Text when copied and pasted. I will not spend any time rebuilding your spreadsheet manually when you can provide the information in a usable format.

While I was able to use the solution suggested by kvsrinivasamurthy, I would like to learn how to use power Query. Here is my data again.

dailyTemperatures_2024.xlsm
	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL
2	Nest	Laid	Emerged	Ave Temp	Inc Days			Nest	Laid	Emerged	Ave Temp	Inc Days
3	GS001	5/3	7/4	80.1	62			GN001	5/4	7/4	80.1	61
4	GS002	5/8	7/5	80.5	58			GN002	5/8	7/3	80.3	56
5	GS003	5/9	7/6	80.5	58			GN003	5/8	7/5	80.5	58
6	GS004	5/13	7/11	80.9	59			GN004	5/10	7/4	80.4	55
7	GS005	5/13	7/11	80.9	59			GN005	5/14	7/5	80.9	52
8	GS006	5/15	7/5	80.9	51			GN006	5/15	7/12	81.1	58
9	GS007	5/17	7/14	81.3	58			GN007	5/15	7/10	81.1	56
10	GS008	5/18	7/17	81.4	60			GN008	5/17	7/12	81.2	56
11	GS009	5/19	7/15	81.5	57			GN009	5/18	7/14	81.4	57
12	GS010	5/20	7/13	81.4	54			GN010	5/19	7/14	81.5	56
13	GS011	5/20	7/10	81.4	51			GN011	5/20	7/14	81.5	55
14	GS012	5/21	7/14	81.7	54			GN012	5/20	7/16	81.5	57
15	GS013	5/21	7/13	81.6	53			GN013	5/24	7/16	81.9	53
16	GS014	5/22	7/13	81.7	52			GN014	5/25	7/14	82.0	50
17	GS015	5/22	7/14	81.8	53			GN015	5/25	7/15	82.0	51
18	GS016	5/23	7/15	81.9	53			GN016	5/25	7/15	82.0	51
19	GS017	5/23	7/17	81.8	55			GN017	5/25	7/13	81.9	49
20	GS018	5/23	7/14	81.8	52			GN018	5/26	7/13	81.9	48
21	GS019	5/24	7/14	81.9	51		nv	GN019	5/26	#N/A	#N/A	#N/A
22	GS020	5/24	7/14	81.9	51			GN020	5/27	7/19	82.0	53
23	GS021	5/25	#N/A	#N/A	#N/A			GN021	5/27	7/15	82.0	49
24	GS022	5/26	7/19	81.9	54			GN022	5/28	7/21	81.9	54
25	GS023	5/29	7/18	82.0	50			GN023	5/29	7/18	82.0	50
26	GS024	5/30	7/20	82.0	51			GN024	5/29	7/25	81.9	57
27	GS025	5/30	7/15	82.1	46			GN025	5/30	7/19	82.0	50
28	GS026	5/30	7/18	82.0	49			GN026	5/30	7/20	82.0	51
29	GS027	5/31	7/24	82.0	54			GN027	5/30	7/25	81.9	56
30	GS028	5/31	7/21	82.0	51			GN028	6/1	7/19	82.1	48
31	GS029	6/1	#N/A	#N/A	#N/A			GN029	6/1	7/23	82.0	52
32	GS030	6/1	7/21	82.1	50			GN030	6/1	7/24	82.1	53
33	GS031	6/1	7/21	82.1	50			GN031	6/2	7/21	82.1	49
34	GS032	6/2	#N/A	#N/A	#N/A			GN032	6/3	7/25	82.2	52
35	GS033	6/5	7/24	82.3	49			GN033	6/4	7/24	82.3	50
36	GS034	6/5	7/27	82.3	52			GN034	6/4	7/23	82.2	49
37	GS035	6/6	7/26	82.4	50			GN035	6/4	7/25	82.3	51
38	GS036	6/6	7/29	82.3	53			GN036	6/4	7/24	82.3	50
39	GS037	6/7	7/30	82.3	53			GN037	6/4	7/25	82.3	51
40	GS038	6/8	7/31	82.2	53			GN038	6/5	7/26	82.3	51
41	GS039	6/9	7/31	82.2	52			GN039	6/6	7/25	82.4	49
42	GS040	6/9	7/29	82.3	50			GN040	6/6	7/26	82.4	50
43	GS041	6/9	7/27	82.3	48			GN041	6/6	7/25	82.4	49
44	GS042	6/10	7/31	82.2	51			GN042	6/7	8/2	82.3	56
45	GS043	6/11	7/29	82.2	48			GN043	6/7	7/26	82.4	49
46	GS044	6/12	7/30	82.1	48			GN044	6/7	#N/A	#N/A	#N/A
47	GS045	6/12	7/31	82.1	49			GN045	6/7	7/26	82.4	49
48	GS046	6/12	7/31	82.1	49			GN046	6/8	7/25	82.4	47
49	GS047	6/13	7/31	82.1	48			GN047	6/8	7/27	82.3	49
SA 2024

Cell Formulas
Range	Formula
BA3:BA49	BA3	='C:\Users\JeffF\Dropbox\gtm\Turtles\2024\[turtleNestCountGTM-2024.xlsm]GTM Data 2024'!$B6
BB3:BB49	BB3	='C:\Users\JeffF\Dropbox\gtm\Turtles\2024\[turtleNestCountGTM-2024.xlsm]GTM Data 2024'!$C6
BC3:BC49	BC3	='C:\Users\JeffF\Dropbox\gtm\Turtles\2024\[turtleNestCountGTM-2024.xlsm]GTM Data 2024'!$K6
BD3:BD49	BD3	=AVERAGE(INDEX(E$2:E$292,MATCH(BB3,B$2:B$292,)):INDEX(E$2:E$292,MATCH((BC3-1),B$2:B$292,)))
BE3:BE49,BL3:BL49	BE3	=BC3-BB3
BH3:BH49	BH3	='C:\Users\JeffF\Dropbox\gtm\Turtles\2024\[turtleNestCountGTM-2024.xlsm]GTM Data 2024'!$M6
BI3:BI49	BI3	='C:\Users\JeffF\Dropbox\gtm\Turtles\2024\[turtleNestCountGTM-2024.xlsm]GTM Data 2024'!$N6
BJ3:BJ49	BJ3	='C:\Users\JeffF\Dropbox\gtm\Turtles\2024\[turtleNestCountGTM-2024.xlsm]GTM Data 2024'!$V6
BK3:BK49	BK3	=AVERAGE(INDEX(E$2:E$292,MATCH(BI3,B$2:B$292,)):INDEX(E$2:E$292,MATCH((BJ3-1),B$2:B$292,)))