Iginla2011

If you have thousands of columns, then excel is clearly not your tool. Even if you had a simple macro that could calculate it for a row, you'd still have to run it on thousands of rows. You could, I suppose, loop through the rows, but you'd still have to analyze a thousand different columns of rankings, which would be 1000 x 10 deciles or 10,000.

Excel just doesn't seem like the appropriate tool.


If you have 30 columns of data, then maybe you can do it. 100 at the outside. The RANK function might come in handy here. TRUNC(10* (RANK(*,*) / COUNT(*))), should get you the decile for each entry. Depending on how the function is implemented, you might have to add 1.

A wrinkle might come in with how you've coded missing data and how rank counts blank entries.

I would make a separate worksheet that gives the decile for each data point in the corresponding master data sheet. You can then get the averages either by doing a SUMIF / COUNTIF equation, or perhaps by running pivot tables.


This is a project more suited to R or Matlab, though.