Issue with Advanced Conditional Array to Determine Median Absolute Deviation

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
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)))))}
 

Some videos you may like

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
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,754
Messages
5,574,040
Members
412,565
Latest member
roberttaekim
Top