Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a spreadsheet calculate faster
I've "inherited" a very complicated spreadsheet at work, which runs quite
slowly. I'm going to try to speed it up a bit. Here are some things I've thought of so far, but I'm not sure if they're going to work. 1. In some places there are complicated formulae depending on many input cells. But if one of those cells is zero then the formula will calculate to zero as well. Would it speed things up to replace "=formula" with "=if(a1=0,0,formula). In other words, does the IF function calculate both parts following the condition (in which case this would just slow it down further) or does it only calculate the relevant part in which case this might save a lot of calculation time. 2. Imagine the first 1000 rows column A has various numbers which will always be integers in the range 1-10 and column B has a complex formula which depends on the figure in the nieghboring cell in column A and also on other fixed cells in the spreadsheet. Would it speed up the spreadsheet to have a small ten row table somewhere in the spreadsheet with the complex forumla worked out for numbers 1-10 and use and replace the formula in column B with a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")? Are there any other good tips for speeding up spreadsheets? Thanks, Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a spreadsheet calculate faster
Hi Eric,
To learn all about Excel's performance and what to do to improve that; visit Charles Williams' site: www.decisionmodels.com -- Kind regards, Niek Otten Microsoft MVP - Excel "EricK" wrote in message ... | I've "inherited" a very complicated spreadsheet at work, which runs quite | slowly. I'm going to try to speed it up a bit. Here are some things I've | thought of so far, but I'm not sure if they're going to work. | | 1. In some places there are complicated formulae depending on many input | cells. But if one of those cells is zero then the formula will calculate to | zero as well. Would it speed things up to replace "=formula" with | "=if(a1=0,0,formula). In other words, does the IF function calculate both | parts following the condition (in which case this would just slow it down | further) or does it only calculate the relevant part in which case this might | save a lot of calculation time. | | 2. Imagine the first 1000 rows column A has various numbers which will | always be integers in the range 1-10 and column B has a complex formula which | depends on the figure in the nieghboring cell in column A and also on other | fixed cells in the spreadsheet. Would it speed up the spreadsheet to have a | small ten row table somewhere in the spreadsheet with the complex forumla | worked out for numbers 1-10 and use and replace the formula in column B with | a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a | complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")? | | Are there any other good tips for speeding up spreadsheets? | | Thanks, | | Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a spreadsheet calculate faster
Question 1: Yes, it would be more efficient. If the conditional test of the
IF function is TRUE it doesn't continue to evaluate the value_if_false. For example: =if(a1=0,0,formula) If A1 = 0 it doesn't continue to calculate "formula". Question 2: Yes, that will also help. Using a combination of INDEX/MATCH is more efficient than VLOOKUP. Visit this site for lots of info on efficiency: http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "EricK" wrote in message ... I've "inherited" a very complicated spreadsheet at work, which runs quite slowly. I'm going to try to speed it up a bit. Here are some things I've thought of so far, but I'm not sure if they're going to work. 1. In some places there are complicated formulae depending on many input cells. But if one of those cells is zero then the formula will calculate to zero as well. Would it speed things up to replace "=formula" with "=if(a1=0,0,formula). In other words, does the IF function calculate both parts following the condition (in which case this would just slow it down further) or does it only calculate the relevant part in which case this might save a lot of calculation time. 2. Imagine the first 1000 rows column A has various numbers which will always be integers in the range 1-10 and column B has a complex formula which depends on the figure in the nieghboring cell in column A and also on other fixed cells in the spreadsheet. Would it speed up the spreadsheet to have a small ten row table somewhere in the spreadsheet with the complex forumla worked out for numbers 1-10 and use and replace the formula in column B with a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")? Are there any other good tips for speeding up spreadsheets? Thanks, Eric |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a spreadsheet calculate faster
Check out this link... There is great info on optimizind speed and memory and
calculations. http://www.decisionmodels.com/index.htm -- HTH... Jim Thomlinson "EricK" wrote: I've "inherited" a very complicated spreadsheet at work, which runs quite slowly. I'm going to try to speed it up a bit. Here are some things I've thought of so far, but I'm not sure if they're going to work. 1. In some places there are complicated formulae depending on many input cells. But if one of those cells is zero then the formula will calculate to zero as well. Would it speed things up to replace "=formula" with "=if(a1=0,0,formula). In other words, does the IF function calculate both parts following the condition (in which case this would just slow it down further) or does it only calculate the relevant part in which case this might save a lot of calculation time. 2. Imagine the first 1000 rows column A has various numbers which will always be integers in the range 1-10 and column B has a complex formula which depends on the figure in the nieghboring cell in column A and also on other fixed cells in the spreadsheet. Would it speed up the spreadsheet to have a small ten row table somewhere in the spreadsheet with the complex forumla worked out for numbers 1-10 and use and replace the formula in column B with a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")? Are there any other good tips for speeding up spreadsheets? Thanks, Eric |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making a spreadsheet calculate faster
Thanks to everyone.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making Windows XP Start 60% Faster | Excel Discussion (Misc queries) | |||
How do I calculate after making 12 payments of my mortgage | Excel Worksheet Functions | |||
Need help making a spreadsheet for my business. | New Users to Excel | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Making Excel Calculate In Correct Order | Excel Discussion (Misc queries) |