[Solved] How to reduce size (large amount similar formula)
Posted: Fri Jan 08, 2010 8:41 am
I have a table with 300(col)*3000(row) ~ 900,000 cells. Most of these cells are formulas. How to reduce the size as formula are similar in rows?
I've tried using matrix but in vain due to the limitation in data range. For example, in cell D1:D3000 entering a formula (using matrix) like {=A1:A3000+1} works as entering A1+1 in D1 and copy D1 to D2, D3, ... and C3000. However, this strategy of reducing the file size fails in several situations, ex: formula =AND(A1:C1) in D1 is not good for matrix as =AND(A1:A3000:C1:C3000) is not valid.
A concrete example is uploaded. Please refer to the sheet named 'Result' and look at the formula in row 2. These formulas are not simple (single structure) but complicated (multiple structure). Furthermore, row 2 to row 50 (even more) are similar by columns. If one copy row 2 to row 3, row 4, ... etc., OO.o calc in face create a new formula according to the source reference (row 2).
The formula is very long compared to raw data. And this enlarge the size of file as more row entered. Is there any way to enhance the efficiency of functionality/filesize?
PS:
Create a new calc file, and compare the difference of using matrix (select range->enter formula->ctrl+shift+enter) and copy & paste, you'll find using matrix is much more efficient than C&P since the formula is defined only one time in former method.
I've tried using matrix but in vain due to the limitation in data range. For example, in cell D1:D3000 entering a formula (using matrix) like {=A1:A3000+1} works as entering A1+1 in D1 and copy D1 to D2, D3, ... and C3000. However, this strategy of reducing the file size fails in several situations, ex: formula =AND(A1:C1) in D1 is not good for matrix as =AND(A1:A3000:C1:C3000) is not valid.
A concrete example is uploaded. Please refer to the sheet named 'Result' and look at the formula in row 2. These formulas are not simple (single structure) but complicated (multiple structure). Furthermore, row 2 to row 50 (even more) are similar by columns. If one copy row 2 to row 3, row 4, ... etc., OO.o calc in face create a new formula according to the source reference (row 2).
The formula is very long compared to raw data. And this enlarge the size of file as more row entered. Is there any way to enhance the efficiency of functionality/filesize?
PS:
Create a new calc file, and compare the difference of using matrix (select range->enter formula->ctrl+shift+enter) and copy & paste, you'll find using matrix is much more efficient than C&P since the formula is defined only one time in former method.