CODEWITHSUNDEEP
excelexcel-formula
udden2903
udden2903

Reputation: 783

Excel - calculating the median without removing duplicates

I have a table that looks like this:

ID  Total
3   3
3   3
3   3
4   11
4   11
4   11
4   11
4   11
4   11
6   9
6   9
7   13
7   13
7   13
7   13
7   13
7   13
7   13
7   13
7   13
7   13
7   13
7   13
7   13

I would like to calculate the median of column B (Total), excluding duplicate combinations of columns A and B. This could be achieved by constructing a table as below, and calculating the median from that table.

ID  Total
3   3
4   11
6   9
7   13

Is there any way of obtaining the median without having to go through this process of manually deleting duplicates?

Upvotes: 0

Views: 925

Answers (2)

XOR LX
XOR LX

Reputation: 7742

=MEDIAN(IF(FREQUENCY(MATCH(A2:A25&"|"&B2:B25,A2:A25&"|"&B2:B25,0),ROW(A2:A25)-MIN(ROW(A2:A25))+1),B2:B25))

Upvotes: 1

Mladen Savic
Mladen Savic

Reputation: 201

There is a way with two additional columns. The first column is concatenation of ID and Total, the second counts occurences of each individual combination. Then you just do the median on those rows where the combination occurs for the first time.

solution

Upvotes: 0

Related Questions