filter; lookup

[toine1]

Hey guys,

I need to find data in the rows next to data that meet the criteria greater than 1 and then the median of those values. Example (I need to find the median of the percentages for all the values that are greater than 1):

1 2%
.02 3%
1.3 4%
1.4 1%

I tried using =VLOOKUP(((B4:B3288)>1),B4:C3288,2,FALSE) and =MEDIAN(VLOOKUP((IF((B4:B3288)>1,value,0)),B4:C3288,2,)>1)

but I get #NA. Or if there is a tool like conditional formatting that will actually select all the values then I can simply put those cells into the median formula.

Thanks!

 

[toine1]

Nevermind! I found a simple way to filter it without changing the rest of my data!

 

[Aladin Akyurek]

Nevermind! I found a simple way to filter it without changing the rest of my data!

The following is probably a better shortcut...

Cintrol+shift+enter, not just enter:

=MEDIAN(IF(B4:B3288>1,C4:C3288))

 

[toine1]

Thank you! That works great!