CODEWITHSUNDEEP
excel
user4634071
user4634071

Reputation:

issues with calculating Median

Here is my data

10	A
12	A
14	B
15	B
15	C
11	D
12	D
10	B
11	A

I want to select A and C from Column B and then calculate the median based on column A.

I have used this function

=MEDIAN(IF(B1:B9="A",B1:B9="C",A1:A9))

But I got a wrong Number. Can we solve this issues? Many thanks for your help.

Upvotes: 1

Views: 2439

Answers (3)

Scott Craner
Scott Craner

Reputation: 152465

Try this aggregate Function:

=AGGREGATE(17,6,$A$1:$A$500/(($B$1:$B$500 = "A")+($B$1:$B$500 = "C")),2)

This is entered as normal formula. It is a non CSE array formula.

enter image description here

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Try this, entered as an array formula by holding down ctrl+shift while you hit Enter

=MEDIAN(IF($B$1:$B$10={"A","C"},$A$1:$A$10))

The IF function returns an array of either the appropriate value in A1:A10, or FALSE. And the MEDIAN function will ignore the logical values, since they are produced by a function, and not manually entered.

Here is a screen shot showing the data and the result. You can see the formula in the formula bar: (Note that the braces {...} around the formula are NOT added manually. They will be added by Excel when you confirm the formula with ctrl+shift+enter. With regard to the array constant, however, those braces you do type in manually.

enter image description here

Upvotes: 2

Baronz
Baronz

Reputation: 677

I don't think you can do it all in one equation. I think you need a new column to create an intermediate value, then solve it.

enter image description here

I put this equation in column C (and dragged it down the column):

=IF(OR(B1="A",B1="B"),A1,"")

And then I tested the values by calculating the Mean (average), Median (middle number), and Mode (most common value). Because there are 6 values, Excel takes the middle of the two.

Here are my equations for column F:

=AVERAGE(C1:C9)
=MEDIAN(C1:C9)
=MODE(C1:C19)

Here is the Microsoft link talking about why the "Median" may be a value between two on the list:

https://support.office.com/en-us/article/MEDIAN-function-D0916313-4753-414C-8537-CE85BDD967D2

Upvotes: 0

Related Questions