I have coded the Median Absolute Deviation in Excel as:

=MEDIAN(IF(ISNUMBER(c10:c14345),ABS(c10:c14345-MEDIAN(c10:c14345))))

This formula works fine on a range of numbers (in this case c10:c14345).

I would like to calculate the formula on a subset of the observations using an IF statement to specify the condition (in my case what country the data is coming from in column D). My formula is:

=MEDIAN(IF(DATA!$D$10:$D$14345=$A308,ABS(DATA!C$10:C$14345-MEDIAN(DATA!C$10:C$14345)))) 

which works well if all the cells contain numeric data. However, in some cases there are missing values and the formula gives an error #Value!.

How can I incorporate the ISNUMBER condition into the formula?

1 Answer

I would suggest using ISNUMBER like this:-

=MEDIAN(IF((Data!$D$10:$D$14345=$A308)*ISNUMBER(Data!C$10:C$14345),ABS(Data!C$10:C$14345-MEDIAN(Data!C$10:C$14345))))

to make sure that it excludes blanks and text values.

Must be entered as an array formula using CtrlShiftEnter

Like your formula, it does the absolute differences from the median of the whole dataset, which I assume is what you want.

Note that the '*' is a way of putting an AND condition in an array formula. It has the effect of coercing the two logical conditions either side of it from true/false to 1/0, then multiplying them together so that if either are false you get a zero result and the test in the IF statement fails.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.