# 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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### 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
260
Replies
5
Views
81
Replies
3
Views
95
Replies
3
Views
51
Replies
8
Views
273