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