Formula averages filtered values

Raspotin

New Member
Joined
Nov 21, 2020
Messages
7
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 😁
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Raspotin

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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,750
Office Version
  1. 365
Platform
  1. Windows
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")
 

Raspotin

New Member
Joined
Nov 21, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. MacOS
I've tried but unfortunately doesn't work....😞
Tweaked it also but in all the cells i got an error
 

Raspotin

New Member
Joined
Nov 21, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

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

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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,750
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,860
Messages
5,627,299
Members
416,237
Latest member
Tethys

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
Top