I wish there was a MedianIF formula...

[Zach]

I've tried using =Median(if( formula to get a result but I'm apparently not doing it correctly. I'm looking for the following items from the Data tab on the attached file:
1. Median (buyers not utilizing envision) - I need a formula to show the median amount from column J based on column M's value of No.
2. Median (buyers utilizing envision) - formula for a median amount from column J based on column M's value of Yes.

And if anyone has any input for the other red letter items that I'm still working on.
3. Minimum amount in column J based on column M's value of No.
4. Maximum amount in Column J based on column M's value of No.
5. Minimum amount in column J based on column M's value of Yes.
6. Maximum amount in Column J based on column M's value of No.

Any help would be appreciated, I looked through other median formula conversations and couldn't seem to piece them together to make a formula work.

 

[eibi]

Zach,

The good news is: There IS a way to do MEDIAN( IF() ) !!

You just need to know the magic word: "Arrays"

Take a look at the attached file. Note the formula in C12 is enclosed in brackets. It's an array formula:

={MEDIAN(IF('Data '!$M$2:$M$219="No",'Data '!$J$2:$J$219))}

You create these type of formulas by typing the formula without the brackets and instead of pressing Enter to execute, you press CTRL + SHIFT + ENTER.

It works the same for MIN, MAX, just like you asked.

={MIN(IF('Data '!$M$2:$M$219="No",'Data '!$J$2:$J$219))}

I bet this will do. Feel free to ask again if not.

 

[Zach]

Eibi
I did not know about that magic word! I appreciate the help as it does exactly what I needed it to. I'm going to start becoming more knowledgeable about Arrays.