Search code examples
excelmedian

How can I find the median of the difference of two rows?

I have two rows in two different sheets in Excel. Each row has 20 elements. I need to find the median of the difference of the corresponding elements in these two rows. I want this output to be in just one cell. I don't want to add another row in my result.

I have:

If Row1 =   p1 p2 p3.... p20
If Row2 =   q1 q2 q3.... q20

I need:

result cell = Median of ((p1 - q1), (p2-q2)....(p20-q20))
Solution 
  • {=MEDIAN(P1:P20-Q1:Q20)}

    This is an array formula you have to validate with Ctrl + Shift + Enter

    [EDIT] Including brettdj's suggestion from the comments below, you can try this formula too:

    {=MEDIAN(Sheet2!A2:T2-Sheet1!A1:T1)}