Hi, I have the following formula =LINEST(J5:O5,$J$2:$O$2,TRUE,FALSE) in cell P5, how do I convert the formula to VBA, as the formula has to be dragged down to row 288098, and so I think it would be wiser to convert it to VBA to decrease the processing time?
Note the formula is a simple linear regression, with the "J5:O5" cells consisting of numbers, and the "$J$2:$O$2" consisting of numbers.
Typically speaking, built in formula is faster than VBA (when there isn't multi-layer dependency or other factors are involved).
As VBA processing is single threaded, formula is faster on quite a few operations.
For an example fastest method for doing lookup, is on sorted data using double approximate lookup.
https://analystcave.com/excel-vlooku...l-performance/
If you want to speed up things, you could use vba to fill the formula and then replace with value, doing 50k rows at a time or so.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Could you elaborate further on how to use vba to fill the formula and then replace with value? sorry I am a bit clueless when it comes to vba
Hi,
May be
Please Login or Register to view this content.
Thanks thats really helpful and works!, only problem is I forgot to mention that the formula is an array formula, and so the formula is repeated in column Q
try
Please Login or Register to view this content.
andPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by mohadin; 08-01-2019 at 09:35 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks