speed up formulas

[jrtaylor]

Edit: see my last post on this thread.


I've got large workbook (about 200,000 cells) which uses Solver. After reading of ways to increase the speed of Excel, one source suggests that I replace SUMIFS formulas with VLOOKUP and INDEX-MATCH. It also says that OFFSET slows down computing time but does not suggest an alternative.

Here are two of my current formulas which use the "slow" method. Can anyone suggest a reformulation of these? If you'd like I can upload a sample of the workbook.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks!

[oeldere]

Did you tried using pivot table for that kind of job?

[JohnTopley]

SUMIF(S) is generally considered very efficient.

For your second fo formula we need to see the file in order to the context: but OFFSET is not the most efficient function.

[jrtaylor]

Hi, I have limited experience with pivot tables, but my understanding is that they do not work when using Solver. the reason I want to slim the formulas is because of heavy use of Solver (otherwise, there's no problem simply entering data or formulas).

Thanks.

[jrtaylor]

I've been unable to find a solution but I didn't want this thread to linger, so I've closed it out. If anyone has a suggestion, feel free to post anyway. Thanks much!