How do you write a single formula for two columns of data to find the median of the second column for all values that are greater than 1 in the first column?

Answer 1

The most critical aspect of this problem is to use an array formula. After you type the formula into a the cell you need to press CTRL+SHIFT+ENTER instead of just ENTER. This lets Excel know you want to evaluate an array (collection of cells in multiple columns) all at once, instead of considering just single cells or columns.

There are three main parts to this equation.

1) Add all the values in column 2 that correspond to specific values in column 1.

=SUM(IF(C3:C12>1,D3:D12,0))

2) Determine how many values in column 2 correspond to specific values in column 1.

=COUNT(IF(C3:C12>1,MATCH(D3:D12,D3:D12,0)))

3) Divide the SUM by the COUNT.

=SUM(IF(C3:C12>1,D3:D12,0))/COUNT(IF(C3:C12>1,MATCH(D3:D12,D3:D12,0)))

EXAMPLE SETUP:

- Put evaluation critera in C3:C12 (values that include > 1)

- Put results in D3:D12 (values for which you want to find the median)

- Put the following formula in D13:

=SUM(IF(C3:C12>1,D3:D12,0))/COUNT(IF(C3:C12>1,MATCH(D3:D12,D3:D12,0)))

After you press CTRL+SHIFT+ENTER you will see the following in the formula bar for D13:

{=SUM(IF(C3:C12>1,D3:D12,0))/COUNT(IF(C3:C12>1,MATCH(D3:D12,D3:D12,0)))}

NOTE: If you see the #VALUE! error message in the cell, that probably means you pressed ENTER instead of the combination of CTRL+SHIFT+ENTER to tell Excel you want to use an array. If you just surround the formula with {} and press enter, you still will get the error message. You must use CTRL+SHIFT+ENTER to enter an array formula.