Excel 2007 : VBA Calculate the average of 10 sub-ranges within a given column of observations

[hsv203]

Hi,

I was wondering if anyone knows how to write a simple macro to calculate a series of averages. I need the macro to split a range of cells in a particular column into 10 equal sub-ranges (without having to specify the initial range length). For each of these sub-ranges I want to calculate the average.

I was thinking that it may be possible to specify a count of the whole column and then in another cell have a formula which divides that count into 10 to give x number of observations for each sub-range which can then be used to calculate the average. I just need it to ensure that all observations are within one of the 10 sub-ranges and that the averages do not overlap. I think that using a count or something similar with no fixed range is preferable, as this would be able to recalculate if observations are added or deleted at a later stage.

[DonkeyOte]

Can I ask why you need to use a Macro for this, it sounds as though you could do this with native functions... will you always be even to create 10 even sized ranges ? What happens if not...

[hsv203]

The problem is that i have a number of worksheets ranging from 300 to 1500+ observations all of which I have to calculate the 10 ranges (ie, from 10 ranges of 30 observations to 10 ranges of 150 observations from which averages need to be calculated). I also need to delete rows(or possibly add them) which will change the total number of observations within each range and therefore the averages for each. I do not necessarily need a macro, however, every formula i have tried to write calculates the wrong average, and manually selecting each range is extremely tedious, particularly when the number of observations change and then need to be re-done.

Any help is very much appreciated!

[DonkeyOte]

I also need to delete rows(or possibly add them) which will change the total number of observations within each range and therefore the averages for each

This is a key requirement - can you elaborate ?

[hsv203]

So for example, if I have 320 obervations within a column, 8 may have to be deleted (because they are outliers, or not within reasonable bounds). The remaining 312 observations would then require partitioning into the 10 sub-ranges and the average calculated.

To be more specific, I am calculating the decile portfolio return for a set of companies in each year.

The macro itself doesnt need to be able to handle the deletion (ie, the observations could be deleted and then the macro run). However, ideally the macro needs to be able to count the number of observations in a particular column, which is then used to divide the observations into 10 ranges and produce a set of 10 averages. Alternatively, even if the number of observations needed to be specified within the macro that would be fine, I just want to avoid having to manually select the ranges for calculating each average.

[DonkeyOte]

OK well let's just start by looking at in Formula terms... if we say you have data in A2:Ax... and you have manually determined that there are an even number of rows by deleting / inserting rows - and all rows contain numerics - then we can say that:

B1: =COUNT(A:A)/10

This obviously gives us Range sizes for each average...

We can then insert the 2 Range Averages in C1:L1
(important we use this range given you could be deleting any row from 2 downwards as part of the "clean up")

Please Login or Register  to view this content.

the above should generate your averages.

If your sheets are all identical in layout you can of course install the above on all sheets simultaneously by grouping the sheets before entering the formulae (remembering to ungroup once complete (by selecting a sheet other than the active one)

[hsv203]

Your formula is magic!! Thank you so much!!

Just one last thing, do you know of anyway that it is possible to correct for having an uneven number of observations through some sort of rounding, obviously without deleting or making things up?!

[DonkeyOte]

It depends really... how do you want to handle ?

Having just re-read your last post... with example of 312 what should happen with the "2" rows that prevent an even split ?

[hsv203]

To be honest, it doesnt really matter how any excess should be split just so that it is as even as possible. For example, with two extra observations as above, you may have 32 in the first decile, 31 in deciles 2 through 9 and 32 again in the 10th decile. With 5 extra observations maybe 32,31,32,31,32,31,32,31,32,31.

I guess the allocation would just depend on what is feasible from a programming perspective... Any ideas?

[DonkeyOte]

OK, I'm no math expert so there's invariably a better approach but for the sake of demo let's do this...

Insert a new sheet into your file, entitled "ADJUST", into which place the below (M/G being A1, 1 to 10 in B1:K1, 0.1 to 0.9 in A2:10, 1's in matrix as appropriate)

Please Login or Register  to view this content.

Back to sheet... to keep things as simple as possible... using our existing setup such that B1 is calculating our COUNT of numbers / 10 and C1:L1 our 10 Averages then I would advise the following to account for possibility of uneven groups:

Please Login or Register  to view this content.

this gives us the decimal remainder
(ie * 10 how many rows preventing even grouping)

then leaving O1 blank (important) you can use formulae in P1 onwards to determine rows per group utilising your ADJUST table:

Please Login or Register  to view this content.

We can now revise our approach in C1:L1 to utilise the data generated in P1:Y1 (using O1 also) such that:

Please Login or Register  to view this content.

As you alter the rows you should find the Averages adjust per rows in each group - if you wish to alter the distribution of the excess rows based on the remainder you can simply alter the 1's in the ADJUST matrix accordingly.

Does that help ?

(as I say I suspect there are preferable approaches to this one...)

[hsv203]

YOU ARE INCREDIBLE!!! Thanks for your help!!!