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

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
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,305
Messages
5,571,445
Members
412,393
Latest member
JayD
Top