Dividing rows into deciles

[lorenzo2207]

Hey,

I'm trying to divide each row in my file into deciles. To be more precise, I have the stocks of an index sorted according their past volatility and want to create 10 equally large portfolios for every month (see attachment). So for example if I have 100 stocks in a month, I want 10 portfolios with 10 stocks in each of the portfolio. But next month it could be that there are 120 stocks sorted, so every portfolio would have 12 stocks. Does anybody know a command or a methode to divide a row into deciles? So that in decile 1 are the first 10% of the stocks, in decile 2 the second 10% of the stocks etc. I would really appreciate an answer, since I don't know how to move on.

Thank you very much!

Edit: I have attached an excel file of an example

My goal is to calculate the average of the first decile cells in a row, than the average of next decile of cells etc.
In my example there are exactly 100 cells so each decile would be exactly 10 cells. But it changes in every row, sometimes there are 150 cells, sometimes 136 cells etc.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[lorenzo2207]

Does anybody have a clue, please?
I still can't figure it out

[AliGW]

You didn't tell us you'd added the workbook - no way of knowing unless you tell us!

I will have a look.

[bebo021999]

In B7 then drag accross:

Please Login or Register  to view this content.

[lorenzo2207]

Thank you for your answer! The formula works for that specific row. But how would do it, if in the next row there are 120 numbers instead of 100, in the next row 123 numbers etc. The number of cells change in every row.

[lorenzo2207]

I attached another workbook to show what I mean. The number of cells in a row change in every row and I want the average from every decile in every row.

[JohnTopley]

Try

=IF(B6="","",AVERAGE(OFFSET($B$3:$K$3,,COUNTA($B$6:B$6)*(COUNTA($3:$3)/10)-(COUNTA($3:$3)/10),,INT(COUNTA($3:$3)/COUNTA($6:$6)))))

What result do you want with 156 entries? above gives deciles of 15.

[lorenzo2207]

Thank you for the answer John!
Could you maybe show me how the formula works on the work book one message before? Can I then just drag the formula down for all rows? For 156 etries it should just divide all the numbers, so four deciles would have 15 entries and six deciles would have 16 entries, in total 156. It should represent stock returns that are divided in 10 equally large portfolios every month, where every row is a separate month.

[JohnTopley]

Based on your posted file:

in B46

=IF(B6="","",AVERAGE(OFFSET($B4:$XFD4,,COUNTA($B$45:B$45)*INT((COUNTA($4:$4)/10+1))-INT(COUNTA($4:$4)/10+1),,INT(COUNTA($4:$4)/COUNTA($45:$45))+1)))

Copy across and down

Just seen your reply so dividing deciles as you described (15s and 16s) will probably require VBA: I certainly don't know how to do it with a formula

[AliGW]

For future reference, if you have multiple rows with a requirement such as you have now described, then there needs to be more than ONE row in the sample workbook for your helpers to see. Try to give us ALL of the information needed at the outset: don't assume that your helper will fill the gaps, because they don't and can't possibly know what's not there.

[lorenzo2207]

Alright no problem John, but thank you very much for the response anyway! I guess I will just simply do it manually then for every row. It will take me a while, but eventually it's done lol

[lorenzo2207]

For future reference, ...

I'm really sorry, its my first post here and didn't want to confuse anyone by posting a ton of data. But of course you are right, a few more rows would have been helpful for you.

[AliGW]

That's why I am advising you.

[lorenzo2207]

Thank you!

[AliGW]

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[JohnTopley]

Please Login or Register  to view this content.

I had to "clear contents" on data INPUTt to get correct column count (column A of INPUT)

[lorenzo2207]

Omg John thank you so so much!! You literally saved my day, I was trying to solve this for the past 1.5 days! Wow I'm so happy, thank you so much John!

[JohnTopley]

Glad to have helped.

[lorenzo2207]

Dear John,

I'm sorry that I have to ask for your help again. I tried to run the code again today for another data set and I got an error.
I just simply switched the input data to another data set. Do you perhaps know how to fix this issue? Thank you very much!

Sincerely
Lorenzo

[JohnTopley]

See attached.

The "problem" is the calculation of the nmber of columns in column A which counts the number of non-blank cells.

It appears that some cells have "data" although they appear blank so I did "Clear Contents" following the last number

[lorenzo2207]

Thank you so so much John, I really appreciate it!!

Sincerely
Lorenzo

[lorenzo2207]

Dear John,

Could I please ask for your help one last time?

I attached a file with the input data and a example output that I want to create. Its not the whole data set, because it would be to large to upload. Basically I want to split each 2 rows into
10 more or less (a decile) equally large parts. In the third row (highlighted yellow) it should calculate the formula that I typed in,
which switches for every decile. I would be really really grateful if you could help me with the splitting of the decile portfolios
and only if possible, the yellow highlighted row. I know I'm asking for a lot and I'm really sorry, but I just don't how to solve this.

Sincerely
Lorenzo

[JohnTopley]

Please Login or Register  to view this content.

[lorenzo2207]

Dear John,
Thank you very much for your quick response! I tried to run the code in the vba editor, but I'm having troubles getting the right results. I copied the code into the vba editor and then tried to run it in the input sheet, but I dont get any results. Would it be possible to send me the macro in the excel file where I could copy my data in, like in the files before? I would really appreciate it!

Thank you a lot for your effort!

Best regards
Lorenzo

[JohnTopley]

See the attached

[lorenzo2207]

Thank you so much John, you are an absolute legend! Thank you thousand times!! I'm really happy!!!!

Best regards
Lorenzo