Hi,
I am hoping someone here will have a creative solution to my problem. I am novice at vba and dynamic ranges always mess me up. I have a set of data, lets say 1000 rows. I want to do percentiles on this data. However it gets a bit more complicated. Of the 1000 rows I have the data broken out in chunks of varying length. For example chunk 1 could be row 1 to 100, chunk 2 is row 101 to 144. Chunk 3 is row 145 to 207. Etc. I want to percentile each of these chunks.
I know the formula to calculate the percentile is =percentile.exc(range, 0.1), then =percentile.exc(range, 0.2), etc until 0.9.
For example on the below data, I want to percentile on the $ column but broken up by set. So my first set of percentiles would be =percentile.exc(C$2$:C$5$, 0.1) and my second set of percentiles would be =percentile.exc(C$6$:C$10$, 0.1). Is there a vba way to determine the data ranges?
I am hoping someone here will have a creative solution to my problem. I am novice at vba and dynamic ranges always mess me up. I have a set of data, lets say 1000 rows. I want to do percentiles on this data. However it gets a bit more complicated. Of the 1000 rows I have the data broken out in chunks of varying length. For example chunk 1 could be row 1 to 100, chunk 2 is row 101 to 144. Chunk 3 is row 145 to 207. Etc. I want to percentile each of these chunks.
I know the formula to calculate the percentile is =percentile.exc(range, 0.1), then =percentile.exc(range, 0.2), etc until 0.9.
For example on the below data, I want to percentile on the $ column but broken up by set. So my first set of percentiles would be =percentile.exc(C$2$:C$5$, 0.1) and my second set of percentiles would be =percentile.exc(C$6$:C$10$, 0.1). Is there a vba way to determine the data ranges?
Item | Set | $ |
1 | 1 | 100 |
2 | 1 | 150 |
3 | 1 | 250 |
4 | 1 | 300 |
5 | 2 | 4 |
6 | 2 | 75 |
7 | 2 | 84 |
8 | 2 | 200 |
9 | 2 | 500 |