Log In
Register
Home
February 19, 2023
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
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.
1 Guest(s)
