Median Array

[stanley thomas]

I have data for a popation with a protein marker and weight of the population in pounds, I am looking for help.
I would like to calculate the median for protein marker for specific weight ( to calculate median for protein marker for 120 to 140 pounds)
can some help me how can I do this in excel.
Many Thanks
Stanley
weight (lbs) protein marker
130 0.815
137 1.608
231 1.122
159 2.216
114 1.005
172 0.695
187 0.841
109 0.561
204 0.909
183 0.446
170 2.329
128 0.740
173 1.914
145 0.869
160 0.707
215 1.154
137 1.714
149 2.412
145 2.045
162 2.758
184 1.229
190 1.272
162 1.322
197 1.312
134 0.950
165 1.229
241 0.125
134 0.743
299 1.224
138 0.442
154 0.731
173 1.413
162 0.374
153 1.610
147 0.700
127 0.370
203 0.528
137 0.729
137 1.502
127 2.310
192 1.084

 

[r1c1]

Hi @stanley thomas

Welcome to Chandoo.org forum and thanks for posting your question. You can use MEDIAN(IF(..)) pattern to solve this problem.

Assuming your data is in columns B & C (B3:C43), below formula gives the answer.

=MEDIAN(IF(($B$3:$B$43>=120)*($B$3:$B$43<=140)=1,$C$3:$C$43))

You must press CTRL+Shift+Enter after typing this formula.

 

[David Evans]

Hi @stanley thomas

Welcome to Chandoo.org forum and thanks for posting your question. You can use MEDIAN(IF(..)) pattern to solve this problem.

Assuming your data is in columns B & C (B3:C43), below formula gives the answer.

=MEDIAN(IF(($B$3:$B$43>=120)*($B$3:$B$43<=140)=1,$C$3:$C$43))

You must press CTRL+Shift+Enter after typing this formula.

Array formulas are elegant solutions, fair play!