Archive of Mr Excel Message Board
Back to Edit in Excel archive index
Back to archive home
Aladin I have a question for ya
Posted by Kevin on January 16, 2002 2:42 PM
I have a spreadsheet in which I am using a formula that I read in one of your posts. It is the sumproduct() function, similar to sumif() but you can specify more than just one condition. The workbook contains one sheet that has a table of data, and three other sheets that contain these sumproduct() functions summarizing the table of data in different ways. There are approx. 16 of these functions on each of the three summary sheets. The function works great, by the way, except for one thing:
My problem is these functions appear to be making my spreadsheet run extremely slow. Each time I enter data into one of the cells in my data table, it takes around 10 seconds to recalculate the spreadsheet. The spreadsheet file itself is not very large, only about 44.5kb. I am at a loss - is it possible that these formulas are slowing down the performance this much? If so, are there any alternatives to using the sumproduct() function that will yield the same results?
Thank you for your help,
Kevin
Re: Aladin I have a question for ya
Posted by Aladin Akyurek on January 16, 2002 3:17 PM
Kevin --
Lets do the following:
Suppose you have in column B from B2 on a sumproduct formula, copied down to B500. Select B3:B500 (leaving out B2), Edit|Copy the range then Edit|Paste Special > Values in place. Do this in other columns where you have such a formula. Save the workbook and reopen it. Try to assess how much performance (including recals) difference there is with and without them. If the difference is significant, we can look at other possibillties.
Aladin
=====
Re: Aladin I have a question for ya
Posted by Kevin on January 17, 2002 6:13 AM
Aladin:
The file is much faster when the formulas are replaced by just values.
Thanks,
--
Re: Aladin I have a question for ya
Posted by Aladin Akyurek on January 17, 2002 7:25 AM
Kevin: You did at least keep the formula in the first cells of the columns I hope?
=========== :
yes, i still have my original formula in the top row
Posted by kevin on January 17, 2002 7:34 AM
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.