How to calculate medians using macro?

[jun22]

Dear all,

I am trying to seek help, in creating a macro which can automatically calculate medians for each name's values.

I am manually calculating medians using formulas in column N, O & P for each name's values (column E). The name and values columns
are generated through another macro, hence it has become a little tedious and time consuming in dragging and changing the cells for each median calculation, for larger datasets.

Here are the formula's I am using to generate median calculations in column N, O & P.

Please Login or Register  to view this content.

I have also attached an example of datasheet and example of the output in the file below.
median_test1.xlsm

Any help would be highly appreciated.

Many thanks.

[joeu2004]

I am trying to seek help, in creating a macro which can automatically calculate medians for each name's values.

I am manually calculating medians using formulas in column N, O & P for each name's values (column E). The name and values columns are generated through another macro, hence it has become a little tedious and time consuming in dragging and changing the cells for each median calculation, for larger datasets.
[....]
I have also attached an example of datasheet and example of the output in the file below.

First, the formulas in column O should be array-entered: press ctrl+shift+Enter instead of just Enter.

They seemed to work (but return the wrong value) only because you normally-entered the formula into one of the rows referenced by the formula. If you copy the text of the formula in O3 from the Formula Bar (don't copy the cell) and paste it into O16, it will return a #VALUE error.

Second, those formulas can be simplified as follows (again, array-enter): =MEDIAN(ABS(N3-E3:E6)), since you calculate =MEDIAN(E1:E6) in N3, and you intend to keep that since you reference N3 in column P.

Finally, the VBA implementation might be easier if you integrate it into the "other macro" that generates the "name and values columns".

The difficulty post facto is finding the range to be used in the MEDIAN formulas and the formulas in column P. I will use the names and values in columns A and E to delimit the ranges. But that is awkward and adds complexity.

You say you want to "calculate medians" in a macro. I assume you want to create the MEDIAN and MAD formulas in columns N and O, as well as the formulas in column P. That way, the calculated values will adapt to any changes in the data as long as the number of data remains the same. It also makes it easier to understand ("audit") the calculations because we can see the formulas.

Note: I took the liberty of optimizing your design, changing the formula in column O to 3.5*MAD so we do not have to repeat 3.5*O3 in each formula in column P.

See the "Modified" worksheet in the attached file.
Attachment 331595

Right-click on the "Modified" worksheet tab, then click on "View Code".

The code is duplicated below.

Please Login or Register  to view this content.

[EDIT] In hindsight, the statement firstRow=Cells(lastRow, "a").End(xlDown).Row can be simplified to firstRow=lastRow+1. (Klunk!)