Sep 04 2019 10:54 AM
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.
Sep 04 2019 11:22 AM
SolutionDid 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)))
Sep 04 2019 11:29 AM
@Subodh_Tiwari_sktneer Thanks for the response. I tried your suggestion but now get a #NUM! error.
Sep 04 2019 11:43 AM
@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!
Sep 04 2019 11:55 AM
You're welcome @AJ_Smith! Glad it worked as desired.
Sep 04 2019 11:22 AM
SolutionDid 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)))