using if function to specify range in formula

A

anjem

I am trying to use the median function, but only apply it to certai
values in a range. It is easy to do this with the average functio
using sumif and dividing it by countif, but is there any way to do thi
on other functions
 
F

Frank Kabel

Hi
use the following array formula (entered with CTRL+SHIFT+ENTER):
=MEDIA(IF(A1:A100=your_value,A1:A100))
 
B

Bernard Liengme

You need to use an array formula.
Example, to find the median for numbers less than 10 in a range:
1) enter =MEDIAN(IF(C16:C25<10,C16:C25))
2) complete the formula using Shift+Ctrl+Enter (NOT just a simple enter)
Excel adds braces { } around the formula

You may want to try =AVERAGE(IF(C16:C25<10,C16:C25)) (entered as an array
formula) to compare with your other method

Best wishes
 
Top