# Issue with Advanced Conditional Array to Determine Median Absolute Deviation

#### Aimee S.

##### Board Regular
Hello Folks.

So I am able to (using CTRL SHIFT ENTER) use the following that conditionally calculates the basic Median of a range based on two criteria:

{=MEDIAN(IF(\$H\$4:\$H\$300000=\$H4,IF(\$K\$4:\$K\$300000=\$K4,\$L\$4:\$L\$300000)))}

I drag this down and have no issues.

However, when I try to implement the median absolute deviation flavor of this I am getting incorrect results. I am sure the syntax is my issue and it is a PEBCAK thing The basis for M.A.D. is =MEDIAN(ABS(range of values - MEDIAN(range of values)))

The range of values for me is \$L\$4:\$L\$300000

The conditions that need to be met to restrict the range of values considered depending on the row are:

\$H\$4:\$H\$300000=\$H4

\$K\$4:\$K\$300000=\$K4

Please help!!! I tried the following but it is wrong. I am sure it has to do with the conditions not being set to restrict the range that the ABS function is considering to match the MEDIAN.

{=MEDIAN(ABS(\$L\$4:\$L\$300000 - MEDIAN(IF(\$H\$4:\$H\$300000=\$H4,IF(\$K\$4:\$K\$300000=\$K4,\$L\$4:\$L\$300000)))))}

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### DanteAmor

##### Well-known Member
It will be that you need this:

Book1
AHKLM
1
3
4x1y1-14
5x2y22
6x1y13
7x4y44
8x1y1-5
9x6y66
10x1y17
sheet
Cell Formulas
RangeFormula
M4M4{=MEDIAN(IF(\$H\$4:\$H\$10=\$H4,IF(\$K\$4:\$K\$10=\$K4,ABS(\$L\$4:\$L\$10))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Replies
4
Views
246
Replies
5
Views
75
Replies
3
Views
94
Replies
8
Views
271
Replies
1
Views
113