Streamlining a complex spreadsheet

**redkilner** · 03-02-2012, 05:30 PM

I have a spreadsheet that analyzes real estate. Some examples of the functions are Price per square foot and columns that clean up "0" entries for graphs. In other columns I have functions that then compile all properties that meet those criteria and were sold between Jan 1st and March 31st. As you can see it gets complex and I end up with many columns of information referring to each other.

My question is, does it make the spreadsheet faster to integrate the price per square foot column into all of the columns referring to it or is it faster to have multiple columns with less complex formulas?

Thanks in advance,

Brian

**DGagnon** · 03-02-2012, 05:35 PM

assuming that price per square foot is a calculation it would slow the sheet down because it would have to recalculate that in each other column.

**redkilner** · 03-02-2012, 05:59 PM

Just to play devils advocate for a second to better understand... If I were to take all of the calculations in each of my formulas and break them down into individual functions in different columns my spreadsheet sounds like it would be huge. Every column would have an =if( function or =iserror( function and would give a true or false. I would then base all of my final statistics based on those true or false statements. The spreadsheet could be 10mb and I could end up with a lot of repetition. If I combine them all I end up with far fewer columns but much more complex formulas. But like you said those simple functions would have to be repeated in each column in which I use them. Is it a "happy medium" or is there a set rule?

Maybe someone can tell me what drives the size of a spread sheet as well. Does it have to do with complexity? Or is it a straight, "One character equals 3 bytes" or whatever?

**DGagnon** · 03-02-2012, 06:47 PM

its not really in the length of the calculating in terms of characters, but in the actual calculation being made. when trying to speed up a file (assumign you mean calculation speed, not file load speed) you want to have as few actual calculations being made as possible. so something like price per sq foot that is called say 7 times would be best to do the calculation one time, then reference it. if you put the calculation in each of those 7 refrences it will actualy recalculated it each time.