CODEWITHSUNDEEP
excelmedian
EmrylRaine
EmrylRaine

Reputation: 11

Excel: How to calculate Median while excluding matching data from another column

I am trying to calculate the median time while excluding a certain data. I have 3 columns of data.
I need to calculate the Column B but exclude times where column A & C match.

Column A Column B Column C
123 1:05 159
456 2:10 987
789 2:55 123
321 3:14
654 4:22
987 1:23
159 0:53
357 3:25

Upvotes: 1

Views: 42

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33165

=MEDIAN(IF((B1:B8)*(ISNA(MATCH(A1:A8,C1:C8,FALSE)))<>0,B1:B8,""))

Enter as an array formula - Ctrl+Shift+Enter, not just Enter.

The MATCH<>0 part returns an array values or NAs depending on whether there's a match - that is, it returns a value if the column A value exists in column C and returns NA if it doesn't.

The ISNA turns those in to TRUE and FALSE. A match will be FALSE because it's not NA.

B1:B8 is multiplied by the array of TRUE and FALSE where TRUE = 1 and FALSE = 0. The IF conditional is comparing this array

{0;0.0902777777777778;0.121527777777778;0.134722222222222;0.181944444444444;0;0;0.142361111111111}

to zero. If you did the MEDIAN from there, the zeros would count. But MEDIAN ignores strings, so the IF statement turns zeros into an empty string. What ultimately gets fed into the MEDIAN function is this array

{"";0.0902777777777778;0.121527777777778;0.134722222222222;0.181944444444444;"";"";0.142361111111111}

MEDIAN ignores those three strings and returns 3:14.

Upvotes: 0

Jeevan Gopinath
Jeevan Gopinath

Reputation: 48

Try this: =MEDIAN(IF($A:$A<>$C:$C,$B:$B))

Upvotes: 0

Related Questions