using multiple IFs with result as calculation

[AJ_Smith]

Hi all,

I am working on the following formula but keep getting a null result.

=IF(AND(raw_data!$B$2:$B$1698=$A18, raw_data!$BA$2:$BA$1698=B$17), MEDIAN(raw_data!$AY$2:$AY$1698), "")

The formula is drawing from a worksheet with data on archaeological ceramics (I am a graduate student in archaeology). I want to calculate the median for values in column AY (a measurement value) IF the ceramic vessel associated with the measurement meets criteria coded in two other columns (B and BA). In this case, only if they are a certain shape and from a certain unit. So, I want to calculate the median measurement for all bowls from Unit 2, for example. 

I hit control-shift-enter to make it an array formula, but still no dice. Cannot for the life of me figure out what is wrong. 

[#2 · Accepted solution]

@AJ_Smith 

Did you try it like this?

=MEDIAN(IF(raw_data!$B$2:$B$1698=$A18,IF( raw_data!$BA$2:$BA$1698=B$17,raw_data!$AY$2:$AY$1698)))

[#3]

@Subodh_Tiwari_sktneer Thanks for the response. I tried your suggestion but now get a #NUM! error.

[#4]

@Subodh_Tiwari_sktneer Ok, I tried entering your formula into a different cell and it seems to work! I think the column where I was entering the formula became formatted strangely--if I tried to change one cell, I would get an error message saying "you can't change part of an array." Not sure how that happened, but starting fresh seemed to get rid of the troublesome formatting. 

Thank-you!

[#5]

You're welcome @AJ_Smith! Glad it worked as desired.