Average of each 25% of column

[rakker]

Bumping an old topic, cause I have the some problem.
How do I calculate the second 25%, the third 25% and last 25% of an range in a column?
With some trial and error I came to this:

second 25%:
AVERAGE(OFFSET(A1,INT(COUNT(A1:A10000)/4),0,INT(COUNT(A1:A10000)/4),1))

third 25%:
AVERAGE(OFFSET(A1,INT(COUNT(A1:A10000)/2),0,INT(COUNT(A1:A10000)/4),1))

last 25%:
dont have yet

Is any of this good?
Can someone plz show me the correct formulas?
Perhaps also the formulas for first, second, third, fourth and last 20% of an range?

[rakker]

BUMP

anyone?

[rakker]

Hereby a workbook with an example.
Can someone show me the formula needed to enter in the ??? cell to give me the average of the last 25% of the range?

[daddylonglegs]

Hello rakker, please don't hijack threads, as per forum rule 2 - start your own thread and link to any other thread you fell is relevant, thanks

In your example I'd question the results for the first 3 formulas - what if C20 is blank? Your formulas will give you the average of C1:C4, C5:C8 and C10:C13 respectively, C9 is missed out.

In that scenario presumably 3 of the average should average 5 cells and one of then 4 cells, how do you expect that to be split up?

I assume there is a variable number of values, will there be any gaps?

[rakker]

Hello rakker, please don't hijack threads, as per forum rule 2 - start your own thread and link to any other thread you fell is relevant, thanks

Sorry, my mistake. Im new here and thought that I could bump an old thread and add a little question, because my question is very similar to the original poster's question. That way, other people would have the answers nicely together in one thread. I now see that this is not allowed, sorry and thank you for moving my posts.

In your example I'd question the results for the first 3 formulas - what if C20 is blank? Your formulas will give you the average of C1:C4, C5:C8 and C10:C13 respectively, C9 is missed out.

You're right, I guess my formulas dont work when there are blank cells in the range.

In that scenario presumably 3 of the average should average 5 cells and one of then 4 cells, how do you expect that to be split up?
I assume there is a variable number of values, will there be any gaps?

I dont really understand your first question.
To your second: there are in total over 100.000 values in one column (column C). The ranges I need to calculate the averages from also vary in size. So, for example:

- 1st, 2nd, 3rd and 4th 25% average of range C1:C480
- 1st, 2nd, 3rd and 4th 25% average of range C481:C685
- 1st, 2nd, 3rd and 4th 25% average of range C686:C780
etc.

There are no gaps or blank cells in the entire column.
In another row I already extracted the ranges, so if I have the correct formulas, I can make all the formulas using the function CONCATENATE.