Dear All,
Currently, in my Excel file, there is data on 16,000 rows and I have formulas from column N to Y on all 16000 rows (due to file size restrictions, I have deleted the formulas on most of the rows). This file is growing and can go up to 50,000 rows and the formula needs to be applied to 50,000 rows.
The problem is that it takes a lot of time for the formulas to calculate the amounts and give the output on all the rows and most of the time it crashes. Also, the file size has become a lot bigger as well.
Is there any way to write a simplified version of the formula that will give the same result and not take too much of time or convert these formulas into Power Query or VBA code to maximise the efficiency and get the desired outcome quickly?
Please see the attached file for your reference.
I would really appreciate your help and your expert advice.
You have a lot of the same calculations repeated in those formulas. I would move the lookups to a new row above the columns, and add the factored trend calculation into a new column, as in the attached. Even with 50,000 rows filled, it's only about 5MB.
Hi Velouria,
Thank you very much for your help and for providing the solution.
it's amazing to see how Expert minds work and how they come up with simple formulas/solutions.
Thanks once again!
