Need help with a function

[r.boghani]

I have 3 columns titled: Ticker, Economic Book Value,Free Cash Flow yield.

The question is "Use excel to find the median free cash flow yield for all tickers in column A with price to economic book values greater than 1"

I need to build one formula in one cell that answers this question.

Heres a snapshot of what the data looks like.

Can someone please help. I have tried if statements but can't get it to work. I know I can auto custom filter to filter all the book values greater than 1, but when i apply the median function it still takes into account ALL free cash flow yields.

Ticker, economic book value, Free cash flow yield
A 1 -5%
AAD 0.4 -4%
AAE 1.4 -5%
AAG 1.7 8%
AAJ 1.1 -10%
AAK 1.4 -5%
AAL 1.1 -15%
AAR 1.3 -15%
AB 1.4 -11%
ABC 1 -14%
ABP 1.5 -2%
ACC 1.8 -9%
ACP 1.8 1%
ACX 1.2 -9%
ADJ 0.9 -7%
ADM 0.8 -1%
ADN 1.7 0%
ADZ 1.7 -10%
AE 0.8 0%
AEM 1.9 12%
AER 0.8 6%
AES 1.4 -10%
AEXQ 0.8 11%
AEZ 1 -15%
AF 1.9 3%
AFC 1.6 6%
AFI 1.3 8%
AFM 0.9 -4%
AFY 1.7 5%
AG 2 -4%

 

[Trouttrap2]

Hello,
Try this: {=MEDIAN(IF(B2:B31>1,C2:C31))}
CTRL+SHIFT+ENTER, not just enter. It is an array formula.

Where column B=Economic Book Value, and column C= Free Cash Flow Yield.

 

[r.boghani]

Thanks so much!
The formula worked!