Same formula in every cell of a column/ storage space reductions

[kux]

Hello,
I have to do some easy calculations with some data from a sales data
base. It is about 55 thousand lines needing 15 MB of storage space.
The data has always the same structure for every product (all stored in
one line).
Now I need to do the same easy calculation for every line (e.g.
average of 36 month sales).

When I enter that formula (and a few other easy and realted
calculations) in every row my used storage space goes up to 130 MB.
This causes Excel to freeze sometimes and makes problems with system
recources... (not a big surprise ...;-)

I think that there must be a way to reduce that because there should
not be too much additional data stored in the calculation (as it is
always the same).

Does anybody know if there is a way to store the formula just once and
then refer to it or so? It is not so important that I have the quickest
runtime possible but the system has to run stable.

Thanks

Kai

 

[Pete_UK]

You should fix the values of your formulae once you have copied them
down the columns - highlight all the cells which have formulae in them,
then click <copy>, then Edit | Paste Special | Values (check) OK and
<Esc>. Use File | Save As to save this file, and it should be
considerably smaller.

Make sure you keep a separate copy of the file with the formulae in the
top row, so you can use this as a template for future use.

Hope this helps.

Pete

 

[kux]

Hello Pete,
thanks for the idea.
Unfortunately I cannot do that because the excel file does some
calculations with changing input data from the sales data base on a
regular basis. The access data base then imports the calculated values
back into access.
I do it that way because the statistical analysis functions in access
are not very comfortable (trend and slope calculations in particular).

Do you have another idea? I just cannot belive that it should be
necessary to store the same formula 50.000 times. that just does not
make any sense to me.

kai