This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I thought I cracked it with this macro but alas not.
I am trying to create a macro that creates median, quartiles and ranks for different subgroups (I am analyzing a large data file where there are 30+ subgroups for which I need to do these calculations).
The output structure I am looking for would be a single line per department per year with columns for value, percentile and median for each measure. What I have created so far s a long table of repeated data.
I have tried looking into the Tile tool as an alternative but I got stuck on how to identify the values for the tile splits.
While I think this is theoretically possible with a macro, I think it gets pretty complex quickly and is somewhat brittle.
I've attached a workflow which I believe accomplishes everything you want without a macro. It does this by using a few different cross tab functions to get each value, and then using some joins to bring the data together.