Median formula with multiple criteria (or not and) and partial text in a cell

[SouthFlorida]

Hi. I'm trying to come up with a formula that allows me to pull the median value from a large data set that meets multiple criteria.

I've attached a sample of the data.

For example, pull all the occupancy numbers from properties containing "*Publix" in Anchors column and with a Building Status of "In Redevelopment," "New Delivery," OR "blank" (not "Under Construction") and give me the median of those. I would use the same formula to look up the median of "*Winn-Dixie."

Thanks for the help.

[Glenn Kennedy]

The problem is the blanks in column A. In F2, copied down:

=IF(A2="",F1,A2)

then

=MEDIAN(IF(ISNUMBER(SEARCH(G2,$C$2:$C$17)),IF($F$2:$F$17<>"Under Construction",$D$2:$D$17,"")))

and

=MEDIAN(IF(ISNUMBER(SEARCH(G5,$C$2:$C$17)),IF($F$2:$F$17<>"Under Construction",$D$2:$D$17,"")))

Both are array formulae.


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[SouthFlorida]

Glenn,

Thank you so much!! It works and I really appreciate it.

As a follow up question, imagine I had a lot of records with different grocery stores and non-grocery stores. If I wanted to find the median of just the grocery stores (Publix, Winn-Dixie, Whole Foods, Trader Joe's, etc.), how could I do that? I tried using the same formula as above and added all the ISNUMBER(SEARCH(G5,$C$2:$C$17)) for the different grocers, but than the formula became too long and it wouldn't work. I'm guessing there might be a way to do it using VLOOKUP. I would still need to not include the Under Construction properties. Then, if I wanted to not include the different grocers, and only pull the median of the non-grocery stores, I would guess I'd just use more of this IF($F$2:$F$17<>"Under Construction", but have it say the different grocers instead of Under Construction. There are about 25 different grocers, so I'm trying to use a formula that will work.

Thanks for all your help.

[Glenn Kennedy]

I set up a named range (CTRL-F3) to pick up the store names for inclusion from G8 to G15. The formula used is:

=Raw!$G$8:INDEX(Raw!$G$8:$G$15,COUNTA(Raw!$G$8:$G$15))

This will pick up ONLY the cells in that range with a value, but ensure that there are NO gaps between stores chosen.

Then, this array formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[SouthFlorida]

Glenn!!! You are amazing! I have no idea how that formula works, but it does...perfectly! Thank you!

I think this might be the last related question for a little while...how do I pull the median of all the properties that don't include those grocers in the name range (Stores)? In other words, show me the median of everything but those grocers.

Thanks so much!

[Glenn Kennedy]

Yep. Althoug I didn't know how to do this 5 minutes ago, either!!

Array formula, again, and manual check below it:

=MEDIAN(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(Stores),Raw!$C$2:$C$17)),0,1),(ROW(Stores)>0)+0)>1,IF($E$2:$E$17<>"Under Construction",Raw!$D$2:$D$17)))

[SouthFlorida]

Again, thank you, Glenn! You are an Excel mastermind! It worked beautifully and makes my life so much easier.

[Glenn Kennedy]

You're welcome!!

[SouthFlorida]

Hi, Glenn. I hope you're doing well.

I'd like to know how I would add an additional criteria to the formula you provided to me last year. In other words, I would like to see the median of all the properties that are not on that list AND that are NOT Under Construction AND are NOT Proposed (these last two are Building Status).

Thanks so much for your help!

[Glenn Kennedy]

Array formula:

=MEDIAN(IF($E$2:$E$17<>"Under Construction",IF($E$2:$E$17<>"Proposed",$D$2:$D$17,"")))

I've also included a manual check.

[SouthFlorida]

Thanks, Glenn! And what would the formula be if I also wanted to exclude the Stores list (h14 formula) and not include Under Construction or Proposed?

I think it would be: MEDIAN(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(Stores),Raw!$C$2:$C$17)),0,1),(ROW(Stores)>0)+0)>1,IF($E$2:$E$17<>"Under Construction",IF($E$2:$E$17<>"Proposed",Raw!$D$2:$D$17,"")))).

That works for me, but then when I put it into my bigger spreadsheet and use the same idea, it doesn't calculate correctly.

This is the formula I'm using, which I tried to do exactly like your example one, but it doesn't work.

MEDIAN(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(Grocers),Data!$F$2:$F$18)),0,1),(ROW(Grocers)>0)+0)>1,IF(Data!$B$2:$B$18<>"Under Construction",IF(Data!$B$2:$B$18<>"Proposed",Data!$K$2:$K$18,""))))

Any thoughts?

Thanks so much!!

[Glenn Kennedy]

This one:

=MEDIAN(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(Stores),Raw!$C$2:$C$17)),0,1),(ROW(Stores)>0)+0)>1,IF($E$2:$E$17<>"Under Construction",IF($E$2:$E$17<>"Proposed",Raw!$D$2:$D$17,""))))

certainly works on "my" sheet (attached). Did all of the previous formulae work on "your" sheet? Check the Grocers Named Range on your sheet. Named ranges sometimes have a tendency to get corrupted on copy/pasting. Failing that, (unfortunately) I do not own a crystal ball, so unless I can see what you have done... I cannot diagnose the problem.

[SouthFlorida]

Hi, Glenn. Thanks. It looks like the same formula that I'm using, but there's something I'm obviously missing. I've attached the spreadsheet that I was referring to. The main problem is the median formula for non-grocery anchored centers on the 2nd tab.

Thanks for your help with this! You are a lifesaver!

[Glenn Kennedy]

Hi. I've been flying across Europe, working and all sorts of stuff. I finally got my head round this, this afternon. Try it now. I did complress the ranges back down to row 19 to help manual checking.

[SouthFlorida]

Hi. Thanks for looking into this. I think the problem may be with the portion of the formula that I need in each of these median formulae (IF(Data!$I$3:$I$1321<>""). Since I have different property sets that I put into this spreadsheet--all with different numbers of rows--I need the formulae to be able to account for that and not "count" any of the blank rows in the Data tab. This is how the original spreadsheet is set up that I sent you (reattached).

Having this in my Totals - Grocery and my Totals - Grocery; Non-Publix works, but for some reason it doesn't when it comes to Totals - Non-Grocery. For some reason, adding that in there looks like it returns the median of all the properties, which is not what I'm looking for.

I do need the formula to have in there those blanks. Any ideas on how to structure it?

Thanks so much for your help!!

[Glenn Kennedy]

If I understand this, it's only the NON-grocery category that needs the non-blank term:

IF(Data!$I$3:$I$50<>"",

This is running down to row 50 (most of whaich are blank) and returns the correct result. If you don't know how many rows you have, you might consider using Dynamic Named Ranges to auto-select the correct range required, excluding hundreds of blank rows from the calculation, minimising delays in processing time.

[SouthFlorida]

Thanks, Glenn!!! This works. I really appreciate you helping me out with this formula! Have a great day.