Using average with other formulas

**DexterG** · 02-13-2013, 01:21 PM

Hi All,

I have 2 columns - A & B, both range for approx 30,000 rows. Column A contains numbers, Col. B is a helper with 1 or 0. In column C,D,E,F - I want to calculate the average for each quartile (or 1st 25%, 2nd 25%, 3rd 25%, and 4th 25%) of cells in column A, but the range is from every time 1 appears in column B till the next 1 appears in column B (thus the range of each last quartile will be till the last zero before the next 1 appears in column B).

To do a COUNTIF with similar conditions I use the following formula,
IF(AND(U2=1,COUNTIF(U3:U$40000,1)),COUNTIF((L2:INDEX(L3:INDEX(L3:L40000,INT((MATCH(1,U3:U40000,0)-1)/4)),)),">0"),"")

Can't get around finding the average. Please help!

Thanks a ton,
Dex

**dilipandey** · 02-13-2013, 01:37 PM

Hi Dex,

Please upload a sample workbook along with your expected results. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**DexterG** · 02-13-2013, 02:51 PM

Please find file attached to help you understand what I am looking for.

Thanks

**dilipandey** · 02-14-2013, 01:00 AM

Hi DexerG, I observed that sometime you are considering 2 number in average function and sometime you are considering 1 or 3 numbers ... any logic for that ? thanks.

Regards,
DILIPandey
<click on below * if this helps>

**DexterG** · 02-14-2013, 06:46 AM

Hi Dilipandey,
That's because am dividing stocks as per quartiles each time (i. 0-25%, 25-50%, 50-75%, 75-100%).

So from the time 1 appears in column B, till the next time 1 appears, if there are 8 cells in between, Column A cells should be divided into 4 groups of 2 each (i.e 25% in each) and the 2 numbers should be averaged, in each quartile.

Similarly from the time 1 appears in column B, till the next time 1 appears, if there are 12 cells in between, cells should be divided into 4 groups of 3 each and 3 numbers should be averaged in each quartile.

Quartile function helps to divide cells into.... well, quartiles!

Alternatively, I can divide the range by 4 - Such as in the COUNTIF function below:
IF(AND(U2=1,COUNTIF(U3:U$40000,1)),COUNTIF((L2:INDEX(L3:INDEX(L3:L40000,INT((MATCH(1,U3:U40000,0)-1)/4)),)),">0"),"")

But I can't seem to find the average incorporating any of the methods above, I am probably getting my formula's wrong as I am still a newbie.

Hoping someone can help me soon.

Thanks,
Dex