Hi all,
I'm just after a few ideas to speed up calculations.
I have a data set of approximately 25 columns, 50000 rows. Ranging over 13 months.
There is a dashboard that has 20 dropdown boxes. These have between 3 and 200 options per drop down.
After the user makes the selections the calculations pull back 10 values per each month and 10 values for the last 4 weeks
These are then used to populate a stacked column chart.
I originally used sumifs & countifs formulas, but due to an issue with numbers I ended up using array formulas.
With a smaller set of data everything is fine but 50000 rows and its taking about 7 seconds to calculate.
I have even used dynamic ranges on all the columns to reduce the data set its looking at.
I have also looked into using ADO to extract the data, but that takes even longer.
Usually for dashboards I have a data set for various measures based on month and weeks and therefore just do look ups. But due to the amount of variables possible from the dropdowns the data sets would be endless.
So I am stuck with calculating the data every time a selection is changed.
The report originally had a sheet for each month and week, with helper columns to check if values matched the dashboard selections. then a sumif formula was used to lookup a value produced by the dropdown selections to find matches in the helper columns.
This was ok but there was also about a 7 second calculation and the file was over 300MB using xls. Using xlsx its about 17mb.
Unfortunately I cannot upload the file as its for work and contains sensitive data.
Has anyone got any suggestions to speed up calculation, or is this going to be my best option due to the amount of selections required.
Bookmarks