Quartile with Multiple condition without using Array

[Siops]

Hi, I have big amount of data and I am trying to do a Quartile formula with multiple condition. My challenge right now is if I use the formula using array, my worksheet takes a lot of time to complete the formula. Is there an alternative formula to do this? Please help. Thank you in advance.

A= Criteria1 Range
B= Criteria2 Range
C= Values

 

[Domenic]

Are you using whole column references? If so, try referencing the actual ranges instead.

Also, which version of Excel are you using? If you're using the latest version where the Filter function is available, try something like this...

=QUARTILE(FILTER(C2:C10,(A2:A10="X")*(B2:B10="Y")),1)

...which returns the first quartile for Column C, where Column A equals "X" and Column B = "Y".

By the way, you should update your profile to include your Excel version, since it could make a difference in the solutions offered.

 

[Siops]

Hi,

Yes, i am using whole column as reference as the worksheet needs to be updated daily.

My Excel version is 2016 which doesn't work the formula. Unfortunately, I cannot update the version as it is company restriction.

 

[Domenic]

In that case, I would suggest that you convert your data into a Table (Ribbon >> Insert tab >> Tables group >> Table), and then use the actual ranges for your formula. The references will automatically adjust as rows are added or removed.

 

[Siops]

But what formula do I need to use for quartiling?

 

[Domenic]

Oh sorry, I thought you already had a formula, but only it was too slow. Okay, no problem. You can use something like this...

=QUARTILE(IF(A2:A100="X",IF(B2:B100="Y",C2:C100)),1)

...which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

 

[Fluff]

Cross posted Quartile with Multiple criteria without using Array

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Siops]

Domenic, yes I do have that formula but even if I convert my data to Tables, it still takes a lot of time to calculate the formula since I have lots of data dump (around 50,000 rows)

Fluff, i apologize for not posting that. It is my fault not looking on the Forum Rules completely.

 

[Domenic]

Then I would suggest using VBA to do the calculations, and place the result in the desired cell.