Easier way to apply formulas to reduce file size?

[Legacy 352679]

Hey everyone,

I've managed to get all my spreadsheet/formulas working fine, but I'm running into an issue where the file size has just become huge.

One tab I've got raw data, and on another tab I've got all my formulas that, when the raw data is entered into the first tab, become populated with the filtered data that I'm interested in (i.e. raw data is in F and one formula is to convert it to C). I've made it so that these formulas on the second tab are applied to every cell in the column (all the way down to 250000 or what ever the limit is) because I'm unsure how much raw data I'm going to have, and wanted it so that any future data could simply be entered and transformed instantly. So as you can imagine, after a couple of rows, there's a lot of data being stored and a lot of cells with formulas not being used. I've also got pivot tables going on a separate tab, but they don't seem to be an issue in this case.

This has resulted in my spreadsheet being 20+mb in size.

Is there any way to make it so that the formulas on the second tab are created only when the raw data is entered? Is it possible to do this using a macro at all, remembering that in the future, additional data will be entered into the spreadsheet? Or do I just have to live with an excessively large excel file?

 

[Peter_SSs]

How far down the second tab do the formulas need to end up going? Is it the same number of rows as there is data on the first tab?
If not, how would we work out how many rows are needed on the second tab?

Where do the formula start on the second tab? Is it row 2?

When you enter more data in the future, is it added below any existing data or do you clear out the old data and put your new data at the top of the first sheet again?

 

[Legacy 352679]

How far down the second tab do the formulas need to end up going? Is it the same number of rows as there is data on the first tab?
If not, how would we work out how many rows are needed on the second tab?

Where do the formula start on the second tab? Is it row 2?

When you enter more data in the future, is it added below any existing data or do you clear out the old data and put your new data at the top of the first sheet again?

That's the thing, I'm unsure how far the formulas need to go down because I'm not sure how much raw data I'm going to end up with. To give clarity, I'm looking at annual daily weather data for the year, so a minimum of 1 to a maximum of 366 data points per year. But that's for one station only, and we could be talking about thousands of stations with similar data. Additionally, the data includes items such as temperature, dew point, attributes etc. So, 366 columns and something around 28 rows is standard for one year.

The second tab has approximately 10 rows, but is inclusive of all the data. I've made it so the formulas go all the way down the sheet so any raw data instantly gets filtered.

When adding data, it gets added to the bottom. It doesn't matter what order it's put in as the pivot table has everything grouped by date.

 

[Peter_SSs]

That's the thing, I'm unsure how far the formulas need to go down

I will re-state my question.

If there are 2500 rows of data on Sheet1, do you need 2500 rows of formulas on Sheet2?
Then if you paste another 1000 rows on Sheet1 do the formulas need to now extend to row 3500 on Sheet2?

If this is not the case, how will we determine how many rows of formulas are needed on Sheet2?

The second tab has approximately 10 rows, but is inclusive of all the data.

I don't understand what that means.

 

[Legacy 352679]

I will re-state my question.

If there are 2500 rows of data on Sheet1, do you need 2500 rows of formulas on Sheet2?
Then if you paste another 1000 rows on Sheet1 do the formulas need to now extend to row 3500 on Sheet2?

If this is not the case, how will we determine how many rows of formulas are needed on Sheet2?


I don't understand what that means.

Sorry, I'm replying on my phone so I may have jumped up what I'm trying to say.

1) Yes and no. Yes to the fact that for 1000 points of data I'll be applying formulas to them. Example a thousand rows of temperature data requires it all to be converted. Any additional raw data added must also be converted. So from 1000 points and an additional 2500 requires 3500 conversions.

But also no. So for example, if there are 28 rows of raw data, the second sheet only applies formulas to the data I'm interested in. It could be as simple as a unit conversion, or displaying values above X as blank cells whilst still showing accompanying data. However the raw data comes in a standard form, so the second sheet may only apply formulas to rows A, C, Z. But, for each data set I'm interested in, a formula must be applied to it. Most of this is for filtering. Example: Dew point for February is in row C, and February 1st is 999, which is the value that indicates no data, so my formula in sheet 2 applies a 'read cell if blank then blank, if cell>900 blank, if not then cell'. This is done for all of row C for every column. Similar for other sets of data.

I hope that made sense?

So in essence, I only have 8 rows of formulas, but every column requires a formula in it for the raw data I'm interested in.

 

[Peter_SSs]

I hope that made sense?

I'm afraid that it doesn't to me, sorry.

BTW, A, C, Z are columns, not rows. Any confusion about that sort of thing can also make your request hard to follow.

only apply formulas to rows A, C, Z.

 

[Michael M]

So you have ALL the required formulas stored on Sheet2 and they get applied to however much data is in Sheet1 ??
I'd sggest a SMALL sample of data with the formulas in their required locations, which I assume will be A,C & Z
Have a look at my tag to download XL2BB, to be able to post said sample.

 

[Legacy 352679]

O

I'm afraid that it doesn't to me, sorry.

BTW, A, C, Z are columns, not rows. Any confusion about that sort of thing can also make your request hard to follow.

H dear lord my bad. No wonder there was confusion. I'm terribly sorry!

 

[Legacy 352679]

So you have ALL the required formulas stored on Sheet2 and they get applied to however much data is in Sheet1 ??
I'd sggest a SMALL sample of data with the formulas in their required locations, which I assume will be A,C & Z
Have a look at my tag to download XL2BB, to be able to post said sample.

I wish I could use a small sample of data, but there could be 28 columns and 366 rows times by 100 stations for one year alone. I can always reduce the file size by doing half the sheet for now, and then the rest of the sheet when the raw data reaches that point, but then I run into the same problem later.

 

[Michael M]

But we only need to see a SMALL sample to try and acertain the best way to provide a solution! It doesn't need to be 28 columns and 366 rows times by 100 stations
So....rather than populating the entire columns on Sheet2 in advance, why not apply the formulas to the raw data whan it is pasted in Sheet2 via a macro.
If this is an option you will need to supply the formulas and which column / columns they go into ??
You don't say which version of Excel you are using, but there could be millions of redundant formulas on the sheet !!