using if function to specify range in formula

  • Thread starter Thread starter anjem
  • Start date Start date
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
 
Hi
use the following array formula (entered with CTRL+SHIFT+ENTER):
=MEDIA(IF(A1:A100=your_value,A1:A100))
 
As an example

=MEDIAN(IF(A1:A10>5,A1:A10))

entered with ctrl + shift & enter
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF, use List as a Range 2
database median function???? 2
Standard Deviation 1
Help with Averages/Sums of values returned from IF function 6
Is there formula similar to SUMIF that will work for average 2
Excel Sumproduct 0
Interesting Median observation 8
can't use function 'countif' in named range 8

Back
Top