Using OR with Median

[Flyboy123]

Hello,

I've been looking for an answer for the following problem but have been unable to find a similar question out there... hopefully someone can help me understand what is going on here. I am trying to determine the median value of a set of numbers but need to apply some criteria. My formula is as follows:

={MEDIAN(IF(OR(A:A="FUN",A:A="HAPPY"),B:B))}

Column A can have 3 types of entries: FUN, HAPPY, BORING
Column B just has plain old numbers in it but for testing purposes I have given entries for BORING a very large set of numbers so I know if they are being included in the final calculation or not.

FUN	10
HAPPY	5
HAPPY	8
FUN	30
BORING	10000
BORING	10000
FUN	20
BORING	10000
BORING	10000
BORING	10000

<tbody>
</tbody>

I'm finding that when I use this formula I am getting a result which is including entries for BORING - i.e. result is 5015. If I remove entries for BORING then the result is 10.

I'd appreciate it if you can tell me why this formula doesn't just pick up values for FUN and HAPPY but also picks up BORING. Additionally... if you can set me straight on a better formula to use I'd be quite pleased.

Many thanks

 

[FDibbins]

Hi, welcome to the board

1. that is an ARRY formula and needs to be entered using CTRL SHIFT ENTEr, not just enter.

2. because that is an array formula, you should restrict the range to what you need, avoid full columns - it could slow your file down. Here is a shorter version...
=MEDIAN($A$1:$A$10={"FUN","HAPPY"},$B$1:$B$10)
also ARRAY entered

 

[Eric W]

Welcome to the forum!

Try:

=MEDIAN(IF((A1:A10="FUN")+(A1:A10="HAPPY"),B1:B10))
with CSE.

Using OR (or AND) in an array formula is usually a bad idea. They tend to evaluate at a point where you don't want them to. Run your formula through the Evaluate Formula tool to see.

Instead, you have to use Boolean operators: + means OR, * means AND. Run my version of your formula through the Evaluate Formula tool to see the difference. Also, using whole column ranges in an array formula is also a bad idea. It makes it very slow. Find the maximum row you think you'll attain and use that instead.

Hope this helps!

 

[Aladin Akyurek]

Let C1:C2 house the criteria FUN and HAPPY.

In C3 control+shift+enter, not just enter:

=MEDIAN(IF(ISNUMBER(MATCH(A2:A1000,C1:C2,0)),B2:B1000))

 

[AlKey]

Here is one for the Excel 2010 and up
regular formula

=AGGREGATE(16,6,B1:B10/(ISNUMBER(MATCH(A1:A10,C1:C2,0))),0.5)

 

[Flyboy123]

I wanted to thank you all for your suggestions. I was honestly surprised to see just how quickly you all joined in to help me out! I appreciate the time you spent on this - I haven't tried out all solutions yet but have worked with 1 or 2 and I'm good to go now!

Many thanks to each one of you!!!

 

[FDibbins]

Thanks for the kind words, always appreciated. Im happy you got what you needed