•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Need to optimize conditional percentile
Our take
Calculating a conditional percentile from a substantial dataset can be challenging, especially with 200,000 rows. Your current formula is thorough but may be causing performance issues due to its complexity. To optimize your approach, consider leveraging more efficient functions or data structures that can handle large datasets effectively. Techniques like using helper columns, array formulas, or even pivot tables can significantly speed up calculations and reduce processing time. Let’s explore strategies that can enhance your workflow and improve the speed of your percentile calculations.
I have a table of data about 200k rows long, and I'm trying to calculate a percentile of a column based on a subset of the data based on keys in other columns. What I am currently using is the following:
```
=percentile.inc(iferror(small(if((table[column1]>0)*(table[column2]=A5)*..., table[column3]), row(table[column1)-1), ""), A12)
```
This is currently taking way too long to run fir how many times I need to repeat this. Any suggestions to optimize the conditional percentile?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Tagged with
#Excel alternatives for data analysis#rows.com#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#cloud-based spreadsheet applications#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#Excel compatibility#row zero#Excel alternatives#conditional percentile#percentile.inc#optimize#data table