Formula averages filtered values

Raspotin

New Member
Joined
Nov 21, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. MacOS
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 ?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
DateUMCSIGDPUMCSI(REAL)TEST^GDPScore
Jan-7883.71.40%55-3.0%10
Apr-7881.616.50%56-2.8%10
Jul-7882.44.00%57-2.6%9
Oct-7879.35.50%58-2.4%9
Jan-7972.10.80%59-2.2%8
Apr-7966.00.50%60-2.0%7
Jul-7960.42.90%61-1.8%6
Oct-7962.11.00%62-1.6%5
Jan-8067.01.30%63-1.4%-10
Apr-8052.7-7.90%64-1.2%-9
Jul-8062.3-0.60%65-1.0%-8
Oct-8075.07.60%66-0.8%-7
Jan-8171.48.50%67-0.6%-6
Apr-8172.4-2.90%68-0.4%-5
Jul-8174.14.70%69-0.2%-4
Oct-8170.3-4.60%700.0%-3
Jan-8271.0-6.50%710.2%-2
Apr-8265.52.20%720.4%-1
Jul-8265.4-1.40%730.6%-1
Oct-8273.40.40%740.8%0
Jan-8370.45.30%751.0%0
Apr-8389.19.40%761.2%1
Jul-8392.88.10%771.4%1
Oct-8389.38.50%781.6%2
Jan-84100.18.20%791.8%2
Apr-8496.17.20%802.0%3
Jul-8496.64.00%812.2%3
Oct-8496.33.20%822.4%4
Jan-8596.04.00%832.6%4
Apr-8594.63.70%842.8%5
Jul-8594.06.40%853.0%5
Oct-8588.43.00%863.2%6
Jan-8695.63.80%873.4%6
Apr-8696.21.90%883.6%7
Jul-8697.74.10%893.8%7
Oct-8695.62.10%904.0%8
Jan-8790.42.80%914.2%8
Apr-8792.84.60%924.4%9
Jul-8793.73.70%934.6%9
Oct-8789.36.80%944.8%10
Jan-8890.82.30%955.0%10
Apr-8891.25.40%965.2%-5
Jul-8893.42.30%975.4%-5
Oct-8894.15.40%985.6%-6
Jan-8997.94.10%995.8%-6
Apr-8991.53.20%1006.0%-7
Jul-8992.03.00%1016.2%-8
Oct-8993.90.90%1026.4%-9
Jan-9093.04.50%1036.6%-9
Apr-9093.91.60%1046.8%-10
Jul-9088.20.10%1057.0%-10
Oct-9063.9-3.40%999
Jan-9166.8-1.90%
Apr-9181.83.10%
Jul-9182.91.90%
Oct-9178.31.80%
Jan-9267.54.80%
Apr-9277.24.50%
 
Upvote 0
Try AVERAGEIFS
Book1
ABCDEFGHIJK
1DateUMCSIGDPUMCSI(REAL)TEST^GDPScoreAverage
21/1/197883.71.40%55-3.00%1060.4
34/1/197881.616.50%56-2.80%10
47/1/197882.44.00%57-2.60%9
510/1/197879.35.50%58-2.40%9
61/1/197972.10.80%59-2.20%8
74/1/1979660.50%60-2.00%7
87/1/197960.42.90%61-1.80%6
910/1/197962.11.00%62-1.60%5
101/1/1980671.30%63-1.40%-10
114/1/198052.7-7.90%64-1.20%-9
127/1/198062.3-0.60%65-1.00%-8
1310/1/1980757.60%66-0.80%-7
141/1/198171.48.50%67-0.60%-6
154/1/198172.4-2.90%68-0.40%-5
167/1/198174.14.70%69-0.20%-4
1710/1/198170.3-4.60%700.00%-3
181/1/198271-6.50%710.20%-2
194/1/198265.52.20%720.40%-1
207/1/198265.4-1.40%730.60%-1
2110/1/198273.40.40%740.80%0
221/1/198370.45.30%751.00%0
234/1/198389.19.40%761.20%1
247/1/198392.88.10%771.40%1
2510/1/198389.38.50%781.60%2
261/1/1984100.18.20%791.80%2
274/1/198496.17.20%802.00%3
287/1/198496.64.00%812.20%3
2910/1/198496.33.20%822.40%4
301/1/1985964.00%832.60%4
314/1/198594.63.70%842.80%5
327/1/1985946.40%853.00%5
3310/1/198588.43.00%863.20%6
341/1/198695.63.80%873.40%6
354/1/198696.21.90%883.60%7
367/1/198697.74.10%893.80%7
3710/1/198695.62.10%904.00%8
381/1/198790.42.80%914.20%8
394/1/198792.84.60%924.40%9
407/1/198793.73.70%934.60%9
4110/1/198789.36.80%944.80%10
421/1/198890.82.30%955.00%10
434/1/198891.25.40%965.20%-5
447/1/198893.42.30%975.40%-5
4510/1/198894.15.40%985.60%-6
461/1/198997.94.10%995.80%-6
474/1/198991.53.20%1006.00%-7
487/1/1989923.00%1016.20%-8
4910/1/198993.90.90%1026.40%-9
501/1/1990934.50%1036.60%-9
514/1/199093.91.60%1046.80%-10
527/1/199088.20.10%1057.00%-10
5310/1/199063.9-3.40%999
541/1/199166.8-1.90%
554/1/199181.83.10%
567/1/199182.91.90%
5710/1/199178.31.80%
581/1/199267.54.80%
594/1/199277.24.50%
Sheet2
Cell Formulas
RangeFormula
K2K2=AVERAGEIFS($B$2:$B$59,$C$2:$C$59,">.028",$C$2:$C$59,"<.030")
 
Upvote 0
I've tried but unfortunately doesn't work....?
Tweaked it also but in all the cells i got an error
 
Upvote 0
DateUMCSIGDPUMCSI(REAL)TEST^GDPScore
gen-7883,71,40%55-3,0%10
#DIV/0!​
apr-7881,616,50%56-2,8%10
#DIV/0!​
lug-7882,44,00%5757,6-2,6%9
#DIV/0!​
ott-7879,35,50%58-2,4%9
#DIV/0!​
gen-7972,10,80%59-2,2%8
#DIV/0!​
apr-7966,00,50%6060,7-2,0%7
#DIV/0!​
lug-7960,42,90%6161,2-1,8%6
#DIV/0!​
ott-7962,11,00%6262,3-1,6%5
#DIV/0!​
gen-8067,01,30%6363,8-1,4%-10
#DIV/0!​
apr-8052,7-7,90%64-1,2%-9
#DIV/0!​
lug-8062,3-0,60%6565,3-1,0%-8
#DIV/0!​
ott-8075,07,60%6666,8-0,8%-7
#DIV/0!​
gen-8171,48,50%6767,7-0,6%-6
#DIV/0!​
apr-8172,4-2,90%68-0,4%-5
#DIV/0!​
lug-8174,14,70%6969,8-0,2%-4
#DIV/0!​
ott-8170,3-4,60%7070,40,0%-3
#DIV/0!​
gen-8271,0-6,50%7171,40,2%-2
#DIV/0!​
apr-8265,52,20%7272,30,4%-1
#DIV/0!​
lug-8265,4-1,40%7373,40,6%-1
#DIV/0!​
ott-8273,40,40%7474,20,8%0
#DIV/0!​
gen-8370,45,30%7575,71,0%0
#DIV/0!​
apr-8389,19,40%7676,51,2%1
#DIV/0!​
lug-8392,88,10%7777,21,4%1
#DIV/0!​
ott-8389,38,50%7878,41,6%2
#DIV/0!​
gen-84100,18,20%7979,31,8%2
#DIV/0!​
apr-8496,17,20%8080,82,0%3
#DIV/0!​
lug-8496,64,00%8181,62,2%3
#DIV/0!​
ott-8496,33,20%8282,62,4%4
#DIV/0!​
gen-8596,04,00%8383,72,6%4
#DIV/0!​
apr-8594,63,70%8484,42,8%5
#DIV/0!​
lug-8594,06,40%8585,43,0%5
#DIV/0!​
ott-8588,43,00%8686,93,2%6
#DIV/0!​
gen-8695,63,80%8787,43,4%6
#DIV/0!​
apr-8696,21,90%8888,33,6%7
#DIV/0!​
lug-8697,74,10%8989,33,8%7
#DIV/0!​
ott-8695,62,10%9090,54,0%8
#DIV/0!​
gen-8790,42,80%9191,44,2%8
#DIV/0!​
apr-8792,84,60%9292,64,4%9
#DIV/0!​
lug-8793,73,70%9393,74,6%9
#DIV/0!​
ott-8789,36,80%9494,44,8%10
#DIV/0!​
gen-8890,82,30%9595,65,0%10
#DIV/0!​
apr-8891,25,40%9696,55,2%-5
#DIV/0!​
lug-8893,42,30%9797,65,4%-5
#DIV/0!​
ott-8894,15,40%9898,15,6%-6
#DIV/0!​
gen-8997,94,10%995,8%-6
#DIV/0!​
apr-8991,53,20%100100,16,0%-7
#DIV/0!​
lug-8992,03,00%101101,46,2%-8
#DIV/0!​
ott-8993,90,90%1026,4%-9
#DIV/0!​
gen-9093,04,50%103103,66,6%-9
#DIV/0!​
apr-9093,91,60%104104,66,8%-10
#DIV/0!​
lug-9088,20,10%105107,57,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 !!!
 
Upvote 0
Try:
Book1
ABCDEFGHIJK
1
22.80%
33%
4
5DateUMCSIGDPUMCSI(REAL)TEST^GDPScoreAverage
61/1/197883.71.40%55-3.00%1060.4
74/1/197881.616.50%56-2.80%10
87/1/197882.44.00%57-2.60%9
910/1/197879.35.50%58-2.40%9
101/1/197972.10.80%59-2.20%8
114/1/1979660.50%60-2.00%7
127/1/197960.42.90%61-1.80%6
1310/1/197962.11.00%62-1.60%5
141/1/1980671.30%63-1.40%-10
154/1/198052.7-7.90%64-1.20%-9
167/1/198062.3-0.60%65-1.00%-8
1710/1/1980757.60%66-0.80%-7
181/1/198171.48.50%67-0.60%-6
194/1/198172.4-2.90%68-0.40%-5
207/1/198174.14.70%69-0.20%-4
2110/1/198170.3-4.60%700.00%-3
221/1/198271-6.50%710.20%-2
234/1/198265.52.20%720.40%-1
247/1/198265.4-1.40%730.60%-1
2510/1/198273.40.40%740.80%0
261/1/198370.45.30%751.00%0
274/1/198389.19.40%761.20%1
287/1/198392.88.10%771.40%1
2910/1/198389.38.50%781.60%2
301/1/1984100.18.20%791.80%2
314/1/198496.17.20%802.00%3
327/1/198496.64.00%812.20%3
3310/1/198496.33.20%822.40%4
341/1/1985964.00%832.60%4
354/1/198594.63.70%842.80%5
367/1/1985946.40%853.00%5
3710/1/198588.43.00%863.20%6
381/1/198695.63.80%873.40%6
394/1/198696.21.90%883.60%7
407/1/198697.74.10%893.80%7
4110/1/198695.62.10%904.00%8
421/1/198790.42.80%914.20%8
434/1/198792.84.60%924.40%9
447/1/198793.73.70%934.60%9
4510/1/198789.36.80%944.80%10
461/1/198890.82.30%955.00%10
474/1/198891.25.40%965.20%-5
487/1/198893.42.30%975.40%-5
4910/1/198894.15.40%985.60%-6
501/1/198997.94.10%995.80%-6
514/1/198991.53.20%1006.00%-7
527/1/1989923.00%1016.20%-8
5310/1/198993.90.90%1026.40%-9
541/1/1990934.50%1036.60%-9
554/1/199093.91.60%1046.80%-10
567/1/199088.20.10%1057.00%-10
5710/1/199063.9-3.40%999
581/1/199166.8-1.90%
594/1/199181.83.10%
607/1/199182.91.90%
6110/1/199178.31.80%
621/1/199267.54.80%
634/1/199277.24.50%
Sheet2
Cell Formulas
RangeFormula
K6K6=AVERAGEIFS($B$6:$B$63,$C$6:$C$63,">"&$H$2,$C$6:$C$63,"<"&$H$3)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top