VBA divide by number of cells used per row

[hhae]

Hi everyone,

I really hope you can help me with this problem.

I have written a code where I put every row of data into deciles (6 deciles) and then take averages of these cells (plus 1 month so 1 row below).
The code does that fine. Only I get the average of all the cells that have the same decile.

At first I thought that excel gave the same number of cells for deciles. So 48 columns = 8 columns per decile (I have 6 deciles)

Only i saw that that is not true. Some deciles have more and some less.
So my question is:
How can I divide by the number of cells used and this has to be adjusted for every row?

If (DECILE_RANK = 1) Then
d11m = d11m + Application.Average(DataSheet.Range(Letter & CStr(i) & ":" & Letter & CStr(i + 1)))
''d11m= decile 1+ 1 month''

DecileSheet.Range("B" + CStr(i)).Value = (d11m / HERE ? what should i divide it by?)

I was thinking of a countif function. But i do not know how to write that to adjust for every row.

Thanks in advance

[Richard Buttrey]

Very difficult to comment without seeing the workbook. Upload it and manually add the results you expect. Don't forget to mention which are the result cells and the calculations you've used to get the results.

[JOHN H. DAVIS]

Not sure if this helps. Test it and see.

Please Login or Register  to view this content.