SOLVED

Highlighted
New Contributor

# using multiple IFs with result as calculation

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.

4 Replies
Highlighted
Best Response confirmed by AJ_Smith (New Contributor)
Solution

# Re: using multiple IFs with result as calculation

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)))

Highlighted

# Re: using multiple IFs with result as calculation

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

Highlighted

# Re: using multiple IFs with result as calculation

@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!

Highlighted

# Re: using multiple IFs with result as calculation

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