Formula averages filtered values

[Raspotin]

Good evening everyone!???

I wanted to kindly ask you for a send as I just couldn't find a solution for over a week now ....

In the column under the writing (TEST)
the formula should be entered, and what I would like to do is calculate the UMCSI values corresponding to <3% and> 2.8% of GDP for example and then make their average

For example here, mean values <3% and> 2.8%
UMCSI GDP
83 2.8%
87 2.9%
82 2.97%
So it should give me the value 84 (which is the UMCSI average)

Thanks so much everyone in advance for the support !! you are great!

P.S (there is already a formula in the cells under TEST that I created with my little knowledge but it doesn't work often or some values are wrong)

Spreadsheet link ?

 

[Raspotin]

Date	UMCSI	GDP			UMCSI(REAL)	TEST	^GDP	Score
Jan-78	83.7	1.40%			55		-3.0%	10
Apr-78	81.6	16.50%			56		-2.8%	10
Jul-78	82.4	4.00%			57		-2.6%	9
Oct-78	79.3	5.50%			58		-2.4%	9
Jan-79	72.1	0.80%			59		-2.2%	8
Apr-79	66.0	0.50%			60		-2.0%	7
Jul-79	60.4	2.90%			61		-1.8%	6
Oct-79	62.1	1.00%			62		-1.6%	5
Jan-80	67.0	1.30%			63		-1.4%	-10
Apr-80	52.7	-7.90%			64		-1.2%	-9
Jul-80	62.3	-0.60%			65		-1.0%	-8
Oct-80	75.0	7.60%			66		-0.8%	-7
Jan-81	71.4	8.50%			67		-0.6%	-6
Apr-81	72.4	-2.90%			68		-0.4%	-5
Jul-81	74.1	4.70%			69		-0.2%	-4
Oct-81	70.3	-4.60%			70		0.0%	-3
Jan-82	71.0	-6.50%			71		0.2%	-2
Apr-82	65.5	2.20%			72		0.4%	-1
Jul-82	65.4	-1.40%			73		0.6%	-1
Oct-82	73.4	0.40%			74		0.8%	0
Jan-83	70.4	5.30%			75		1.0%	0
Apr-83	89.1	9.40%			76		1.2%	1
Jul-83	92.8	8.10%			77		1.4%	1
Oct-83	89.3	8.50%			78		1.6%	2
Jan-84	100.1	8.20%			79		1.8%	2
Apr-84	96.1	7.20%			80		2.0%	3
Jul-84	96.6	4.00%			81		2.2%	3
Oct-84	96.3	3.20%			82		2.4%	4
Jan-85	96.0	4.00%			83		2.6%	4
Apr-85	94.6	3.70%			84		2.8%	5
Jul-85	94.0	6.40%			85		3.0%	5
Oct-85	88.4	3.00%			86		3.2%	6
Jan-86	95.6	3.80%			87		3.4%	6
Apr-86	96.2	1.90%			88		3.6%	7
Jul-86	97.7	4.10%			89		3.8%	7
Oct-86	95.6	2.10%			90		4.0%	8
Jan-87	90.4	2.80%			91		4.2%	8
Apr-87	92.8	4.60%			92		4.4%	9
Jul-87	93.7	3.70%			93		4.6%	9
Oct-87	89.3	6.80%			94		4.8%	10
Jan-88	90.8	2.30%			95		5.0%	10
Apr-88	91.2	5.40%			96		5.2%	-5
Jul-88	93.4	2.30%			97		5.4%	-5
Oct-88	94.1	5.40%			98		5.6%	-6
Jan-89	97.9	4.10%			99		5.8%	-6
Apr-89	91.5	3.20%			100		6.0%	-7
Jul-89	92.0	3.00%			101		6.2%	-8
Oct-89	93.9	0.90%			102		6.4%	-9
Jan-90	93.0	4.50%			103		6.6%	-9
Apr-90	93.9	1.60%			104		6.8%	-10
Jul-90	88.2	0.10%			105		7.0%	-10
Oct-90	63.9	-3.40%			999
Jan-91	66.8	-1.90%
Apr-91	81.8	3.10%
Jul-91	82.9	1.90%
Oct-91	78.3	1.80%
Jan-92	67.5	4.80%
Apr-92	77.2	4.50%

 

[AhoyNC]

Try AVERAGEIFS

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	UMCSI	GDP			UMCSI(REAL)	TEST	^GDP	Score		Average
2	1/1/1978	83.7	1.40%			55		-3.00%	10		60.4
3	4/1/1978	81.6	16.50%			56		-2.80%	10
4	7/1/1978	82.4	4.00%			57		-2.60%	9
5	10/1/1978	79.3	5.50%			58		-2.40%	9
6	1/1/1979	72.1	0.80%			59		-2.20%	8
7	4/1/1979	66	0.50%			60		-2.00%	7
8	7/1/1979	60.4	2.90%			61		-1.80%	6
9	10/1/1979	62.1	1.00%			62		-1.60%	5
10	1/1/1980	67	1.30%			63		-1.40%	-10
11	4/1/1980	52.7	-7.90%			64		-1.20%	-9
12	7/1/1980	62.3	-0.60%			65		-1.00%	-8
13	10/1/1980	75	7.60%			66		-0.80%	-7
14	1/1/1981	71.4	8.50%			67		-0.60%	-6
15	4/1/1981	72.4	-2.90%			68		-0.40%	-5
16	7/1/1981	74.1	4.70%			69		-0.20%	-4
17	10/1/1981	70.3	-4.60%			70		0.00%	-3
18	1/1/1982	71	-6.50%			71		0.20%	-2
19	4/1/1982	65.5	2.20%			72		0.40%	-1
20	7/1/1982	65.4	-1.40%			73		0.60%	-1
21	10/1/1982	73.4	0.40%			74		0.80%	0
22	1/1/1983	70.4	5.30%			75		1.00%	0
23	4/1/1983	89.1	9.40%			76		1.20%	1
24	7/1/1983	92.8	8.10%			77		1.40%	1
25	10/1/1983	89.3	8.50%			78		1.60%	2
26	1/1/1984	100.1	8.20%			79		1.80%	2
27	4/1/1984	96.1	7.20%			80		2.00%	3
28	7/1/1984	96.6	4.00%			81		2.20%	3
29	10/1/1984	96.3	3.20%			82		2.40%	4
30	1/1/1985	96	4.00%			83		2.60%	4
31	4/1/1985	94.6	3.70%			84		2.80%	5
32	7/1/1985	94	6.40%			85		3.00%	5
33	10/1/1985	88.4	3.00%			86		3.20%	6
34	1/1/1986	95.6	3.80%			87		3.40%	6
35	4/1/1986	96.2	1.90%			88		3.60%	7
36	7/1/1986	97.7	4.10%			89		3.80%	7
37	10/1/1986	95.6	2.10%			90		4.00%	8
38	1/1/1987	90.4	2.80%			91		4.20%	8
39	4/1/1987	92.8	4.60%			92		4.40%	9
40	7/1/1987	93.7	3.70%			93		4.60%	9
41	10/1/1987	89.3	6.80%			94		4.80%	10
42	1/1/1988	90.8	2.30%			95		5.00%	10
43	4/1/1988	91.2	5.40%			96		5.20%	-5
44	7/1/1988	93.4	2.30%			97		5.40%	-5
45	10/1/1988	94.1	5.40%			98		5.60%	-6
46	1/1/1989	97.9	4.10%			99		5.80%	-6
47	4/1/1989	91.5	3.20%			100		6.00%	-7
48	7/1/1989	92	3.00%			101		6.20%	-8
49	10/1/1989	93.9	0.90%			102		6.40%	-9
50	1/1/1990	93	4.50%			103		6.60%	-9
51	4/1/1990	93.9	1.60%			104		6.80%	-10
52	7/1/1990	88.2	0.10%			105		7.00%	-10
53	10/1/1990	63.9	-3.40%			999
54	1/1/1991	66.8	-1.90%
55	4/1/1991	81.8	3.10%
56	7/1/1991	82.9	1.90%
57	10/1/1991	78.3	1.80%
58	1/1/1992	67.5	4.80%
59	4/1/1992	77.2	4.50%
Sheet2

Cell Formulas
Range	Formula
K2	K2	=AVERAGEIFS($B$2:$B$59,$C$2:$C$59,">.028",$C$2:$C$59,"<.030")

 

[Raspotin]

I've tried but unfortunately doesn't work....?
Tweaked it also but in all the cells i got an error

 

[Raspotin]

I've tried but unfortunately doesn't work....?
Tweaked it also but in all the cells i got an error

Maybe is beacause i've selected cells instead of inserting the value manually?

 

[Raspotin]

Date	UMCSI	GDP			UMCSI(REAL)	TEST	^GDP	Score
gen-78	83,7	1,40%			55		-3,0%	10		#DIV/0!​
apr-78	81,6	16,50%			56		-2,8%	10		#DIV/0!​
lug-78	82,4	4,00%			57	57,6	-2,6%	9		#DIV/0!​
ott-78	79,3	5,50%			58		-2,4%	9		#DIV/0!​
gen-79	72,1	0,80%			59		-2,2%	8		#DIV/0!​
apr-79	66,0	0,50%			60	60,7	-2,0%	7		#DIV/0!​
lug-79	60,4	2,90%			61	61,2	-1,8%	6		#DIV/0!​
ott-79	62,1	1,00%			62	62,3	-1,6%	5		#DIV/0!​
gen-80	67,0	1,30%			63	63,8	-1,4%	-10		#DIV/0!​
apr-80	52,7	-7,90%			64		-1,2%	-9		#DIV/0!​
lug-80	62,3	-0,60%			65	65,3	-1,0%	-8		#DIV/0!​
ott-80	75,0	7,60%			66	66,8	-0,8%	-7		#DIV/0!​
gen-81	71,4	8,50%			67	67,7	-0,6%	-6		#DIV/0!​
apr-81	72,4	-2,90%			68		-0,4%	-5		#DIV/0!​
lug-81	74,1	4,70%			69	69,8	-0,2%	-4		#DIV/0!​
ott-81	70,3	-4,60%			70	70,4	0,0%	-3		#DIV/0!​
gen-82	71,0	-6,50%			71	71,4	0,2%	-2		#DIV/0!​
apr-82	65,5	2,20%			72	72,3	0,4%	-1		#DIV/0!​
lug-82	65,4	-1,40%			73	73,4	0,6%	-1		#DIV/0!​
ott-82	73,4	0,40%			74	74,2	0,8%	0		#DIV/0!​
gen-83	70,4	5,30%			75	75,7	1,0%	0		#DIV/0!​
apr-83	89,1	9,40%			76	76,5	1,2%	1		#DIV/0!​
lug-83	92,8	8,10%			77	77,2	1,4%	1		#DIV/0!​
ott-83	89,3	8,50%			78	78,4	1,6%	2		#DIV/0!​
gen-84	100,1	8,20%			79	79,3	1,8%	2		#DIV/0!​
apr-84	96,1	7,20%			80	80,8	2,0%	3		#DIV/0!​
lug-84	96,6	4,00%			81	81,6	2,2%	3		#DIV/0!​
ott-84	96,3	3,20%			82	82,6	2,4%	4		#DIV/0!​
gen-85	96,0	4,00%			83	83,7	2,6%	4		#DIV/0!​
apr-85	94,6	3,70%			84	84,4	2,8%	5		#DIV/0!​
lug-85	94,0	6,40%			85	85,4	3,0%	5		#DIV/0!​
ott-85	88,4	3,00%			86	86,9	3,2%	6		#DIV/0!​
gen-86	95,6	3,80%			87	87,4	3,4%	6		#DIV/0!​
apr-86	96,2	1,90%			88	88,3	3,6%	7		#DIV/0!​
lug-86	97,7	4,10%			89	89,3	3,8%	7		#DIV/0!​
ott-86	95,6	2,10%			90	90,5	4,0%	8		#DIV/0!​
gen-87	90,4	2,80%			91	91,4	4,2%	8		#DIV/0!​
apr-87	92,8	4,60%			92	92,6	4,4%	9		#DIV/0!​
lug-87	93,7	3,70%			93	93,7	4,6%	9		#DIV/0!​
ott-87	89,3	6,80%			94	94,4	4,8%	10		#DIV/0!​
gen-88	90,8	2,30%			95	95,6	5,0%	10		#DIV/0!​
apr-88	91,2	5,40%			96	96,5	5,2%	-5		#DIV/0!​
lug-88	93,4	2,30%			97	97,6	5,4%	-5		#DIV/0!​
ott-88	94,1	5,40%			98	98,1	5,6%	-6		#DIV/0!​
gen-89	97,9	4,10%			99		5,8%	-6		#DIV/0!​
apr-89	91,5	3,20%			100	100,1	6,0%	-7		#DIV/0!​
lug-89	92,0	3,00%			101	101,4	6,2%	-8		#DIV/0!​
ott-89	93,9	0,90%			102		6,4%	-9		#DIV/0!​
gen-90	93,0	4,50%			103	103,6	6,6%	-9		#DIV/0!​
apr-90	93,9	1,60%			104	104,6	6,8%	-10		#DIV/0!​
lug-90	88,2	0,10%			105	107,5	7,0%	-10		#DIV/0!​

Yes ...when i select a cell instead of input manually comes wrong ..is there some way to select the cell instead of using the input manually ? like <H2 and >H3 ,
Thanks a lot by the way !!!

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2								2.80%
3								3%
4
5	Date	UMCSI	GDP			UMCSI(REAL)	TEST	^GDP	Score		Average
6	1/1/1978	83.7	1.40%			55		-3.00%	10		60.4
7	4/1/1978	81.6	16.50%			56		-2.80%	10
8	7/1/1978	82.4	4.00%			57		-2.60%	9
9	10/1/1978	79.3	5.50%			58		-2.40%	9
10	1/1/1979	72.1	0.80%			59		-2.20%	8
11	4/1/1979	66	0.50%			60		-2.00%	7
12	7/1/1979	60.4	2.90%			61		-1.80%	6
13	10/1/1979	62.1	1.00%			62		-1.60%	5
14	1/1/1980	67	1.30%			63		-1.40%	-10
15	4/1/1980	52.7	-7.90%			64		-1.20%	-9
16	7/1/1980	62.3	-0.60%			65		-1.00%	-8
17	10/1/1980	75	7.60%			66		-0.80%	-7
18	1/1/1981	71.4	8.50%			67		-0.60%	-6
19	4/1/1981	72.4	-2.90%			68		-0.40%	-5
20	7/1/1981	74.1	4.70%			69		-0.20%	-4
21	10/1/1981	70.3	-4.60%			70		0.00%	-3
22	1/1/1982	71	-6.50%			71		0.20%	-2
23	4/1/1982	65.5	2.20%			72		0.40%	-1
24	7/1/1982	65.4	-1.40%			73		0.60%	-1
25	10/1/1982	73.4	0.40%			74		0.80%	0
26	1/1/1983	70.4	5.30%			75		1.00%	0
27	4/1/1983	89.1	9.40%			76		1.20%	1
28	7/1/1983	92.8	8.10%			77		1.40%	1
29	10/1/1983	89.3	8.50%			78		1.60%	2
30	1/1/1984	100.1	8.20%			79		1.80%	2
31	4/1/1984	96.1	7.20%			80		2.00%	3
32	7/1/1984	96.6	4.00%			81		2.20%	3
33	10/1/1984	96.3	3.20%			82		2.40%	4
34	1/1/1985	96	4.00%			83		2.60%	4
35	4/1/1985	94.6	3.70%			84		2.80%	5
36	7/1/1985	94	6.40%			85		3.00%	5
37	10/1/1985	88.4	3.00%			86		3.20%	6
38	1/1/1986	95.6	3.80%			87		3.40%	6
39	4/1/1986	96.2	1.90%			88		3.60%	7
40	7/1/1986	97.7	4.10%			89		3.80%	7
41	10/1/1986	95.6	2.10%			90		4.00%	8
42	1/1/1987	90.4	2.80%			91		4.20%	8
43	4/1/1987	92.8	4.60%			92		4.40%	9
44	7/1/1987	93.7	3.70%			93		4.60%	9
45	10/1/1987	89.3	6.80%			94		4.80%	10
46	1/1/1988	90.8	2.30%			95		5.00%	10
47	4/1/1988	91.2	5.40%			96		5.20%	-5
48	7/1/1988	93.4	2.30%			97		5.40%	-5
49	10/1/1988	94.1	5.40%			98		5.60%	-6
50	1/1/1989	97.9	4.10%			99		5.80%	-6
51	4/1/1989	91.5	3.20%			100		6.00%	-7
52	7/1/1989	92	3.00%			101		6.20%	-8
53	10/1/1989	93.9	0.90%			102		6.40%	-9
54	1/1/1990	93	4.50%			103		6.60%	-9
55	4/1/1990	93.9	1.60%			104		6.80%	-10
56	7/1/1990	88.2	0.10%			105		7.00%	-10
57	10/1/1990	63.9	-3.40%			999
58	1/1/1991	66.8	-1.90%
59	4/1/1991	81.8	3.10%
60	7/1/1991	82.9	1.90%
61	10/1/1991	78.3	1.80%
62	1/1/1992	67.5	4.80%
63	4/1/1992	77.2	4.50%
Sheet2

Cell Formulas
Range	Formula
K6	K6	=AVERAGEIFS($B$6:$B$63,$C$6:$C$63,">"&$H$2,$C$6:$C$63,"<"&$H$3)