[Unsolved] Modifying formula to include quartile (Little urgent)

[DexterG]

Hi All,

The following formula helps me to find out the count of positive numbers in Column L, dependent on range based on a condition in Column U.

IF(AND(U2=1,COUNTIF(U3:U$40000,1)),COUNTIF(L2:INDEX(L3:L$40000,MATCH(1,U3:U$40000,0)-1),">0"),"")

How do I modify this formula to find just the count of positive numbers in quartile 1 of Column L (or the first 25% cells) for the range derived (as mentioned above, from column U)? Post that quartile 2, 3 & 4 - but if you short on time, please help me with one quartile i'll try to work out the others.

Can't seem to get my way around it since hours, please help!

Thanks a ton,
Dex

[bebo021999]

How about adjusting the range to U3:U10000?

[DexterG]

Thanks bebo, but that won't help.

In reality there are 38776 cells in all in Column U. But that doesn't matter.

The formula basically counts values ">0" in Column L every time time 1 appears in column U, till the next 1 appears. And 1 appears nearly 127 times. Thus instead of manually entering ranges each time I would prefer to include the quartile formula in the above formula to find just the count of positive numbers in quartile 1 (or the first 25% cells of each range) in Column L for the range derived each time from column U.

Hope I've explained well enough and not confused you.

Regards,
Dex

[ChemistB]

So you're saying that you want to base the quartile on what row the final cell is in? doesn't matter if it's 0 or positive number? Can it be text?

[ChemistB]

This formula defines your 1st quartile range

INDEX(U3:INDEX(U3:U10000,INT((MATCH(2,U3:U10000,1)-2)/4)),))
Assumes that you only have 0's and 1's. If you have larger numbers, the 2 in the Match must be increased to be greater than them (i.e 10, or 1E100)
Hope that helps

[DexterG]

Thanks for looking ChemistB.

All are numbers or blank.

Perhaps the excel attached will give you a better idea of what I am trying to find.

Book1 -Quartile.xlsx

[DexterG]

Sorry but that returns a zero. Besides I don't think that is the correct solution I am looking for.

Can you please have a quick look at the excel attached in the last post above by me. Will help understand what I mean.

Thanks,
Dex

[ChemistB]

Here are the four formulas. They broke things up differently only in 1 group (I highlighted it to show how the formulas broke up the group). All the formulas can be dragged/copied down the sheet
Q1

Please Login or Register  to view this content.

Q2

Please Login or Register  to view this content.

Q3

Please Login or Register  to view this content.

Q4

Please Login or Register  to view this content.

See attachment
Does that work for you?

[DexterG]

Thanks ChemistB, works perfect!!!

Finally relief!! Thanks again!!