Easier way to apply formulas to reduce file size?

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,246
Office Version
  1. 365
Platform
  1. Windows
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.
Couldn't we have some dummy data that was 4 columns and 5 rows times 2 stations and 2 years using XL2BB just to demonstrate the concept?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
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 !!
I'll be able to provide the formulas tomorrow. If this can be applied through a macro for new data that's entered that would be fantastic, I just wouldn't know how to go about setting it up. But I'll reply tomorrow with everything. It's 2016 as far as I'm aware.

But you're correct, I have redundant formulas just sitting not doing anything. The intention was to have it so that who ever used it next to add data would just have the results straight away, as I won't be the only person adding to the database.
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
Ill
Couldn't we have some dummy data that was 4 columns and 5 rows times 2 stations and 2 years using XL2BB just to demonstrate the concept?
I'll provide the data tomorrow when I'm back at my workstation. Would it be easier if I just make a dummy excel file for you and attach it?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,029
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ill

I'll provide the data tomorrow when I'm back at my workstation. Would it be easier if I just make a dummy excel file for you and attach it?
You can make a file and upload to a hosting site, Dropbox for instance. Then post the link back here
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43

ADVERTISEMENT

So unfortunately my workplace doesn't allow access to dropboxes, not this website so I'm going to try my best to demonstrate my formulas in tables written on my phone. So apologies for any additional confusion.

Example raw data that goes into sheet 1:
StationDateTempSTPVISIBGUSTMINMAX
101001/01/19902018.9999322999
101002/01/19902514.65649996999
101003/01/19902317.19993299965
This is only an example of the data, as the columns go to AB. The data is for daily information for the year.

Formulas in sheet 2:

Column a: =IF('Raw Data'!F2&" "&'Raw Data'!A2="","",('Raw Data'!F2&" "&'Raw Data'!A2)

Column b: =IF(R2="",01/01/1900,R2)
Column r: =IF('Raw Data'!B2="","",'Raw Data'!B2)
The reason why I have the date the way I do is, I have a pivot table on sheet 3 that reads all the cells for column A - L in sheet 2. I needed dates in every cell for column B so that the pivot table would continue to group by dates properly and automatically, so when someone was entering new raw data into sheet 1, all they had to do was refresh the pivot table in sheet 3 and it would continue to group by date with the new data. I wanted to avoid people having to update the data select range because not everyone is comfortable with excel.

column C: =IF('Raw Data'!G2="","", IF('Raw Data'!G2>0.1, IF('Raw Data'!G2<900, CONVERT('Raw Data'!G2"F","C"))))
column d: =IF('Raw Data'!I2="","", IF('Raw Data'!I2>0.1, IF('Raw Data'!G2<900, CONVERT('Raw Data'!I2"F","C"))))

The reason why I have >0.1 and <900 is because I'm converting from F to C. Any value that is 0 is -17.8, and since the formula is applied to every cell in column C and D on sheet 2, all the cells that don't have raw data in sheet 1 are being treated as 0 in sheet 2, and therefore when I was doing my averages in my pivot table in sheet 3, given that I was using every cell for these columns, it was skewing the data. Of course, if I can generate a macro for sheet 2 to only read raw data that is populated, that would then change the need to filter out 0 data.

The reason for the 900 is because every data point that is missing within the data is 999 or 9999. It's a **** way the raw data is given.

Column E: =IFERROR(S2,"")
Column S: =IF('Raw Data'!G2="","",100*(EXP((17.625*D2)/(243+D2))/EXP((17.25*C2)/(243.04+C2))))
Again, I have the if error formula because of the pivot table and so that the cell in column E would be blank and not skew the data.

Column F-L are simple filtering formulas that are along the long the lines of =IF(RAW DATA"","",IF(RAW DATA>999,"",RAW DATA))

A lot of this is pretty much just for the pivot table. If I didn't have/need that, then I could just click and drag the formulas down and save a lot of space. But because the pivot table reads every cell from A-L in sheet 2, and because I wanted to keep the grouping in dates, I've done it this way.

So far everything works, I'm just looking to reduce the file size.

I apologise if this ends up being formatted poorly. I'm on my phone again as there are restrictions at work.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,029
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
This code will populate Sheet2 in the columns you have provided the information for down as far as there is data in the RawData sheet
I haven't provided formulas for Columns F-L, as you haven't given us specific formulas to translate.
Make sure you try this on a copy of your workbook, in case it isn't what you actually needed....Also make sure sheet 2 is empty of ALL formulas

VBA Code:
Sub MM1()
Dim lr As Long, r As Long
lr = Sheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet2")
    .Range("A2:A" & lr).Formula = "=IF('Raw Data'!F2&"" ""&'Raw Data'!A2="""","""",('Raw Data'!F2&"" ""&'Raw Data'!A2)"
    .Range("B2:B" & lr).Formula = "=IF(R2="""",01/01/1900,R2)"
    .Range("C2:C" & lr).Formula = "=IF('Raw Data'!G2="""","""", IF(AND('Raw Data'!G2>0.1,'Raw Data'!G2<900), CONVERT('Raw Data'!G2""F"",""C"")))"
    .Range("D2:D" & lr).Formula = "=IF('Raw Data'!I2="""","""", IF(AND('Raw Data'!I2>0.1, 'Raw Data'!G2<900), CONVERT('Raw Data'!I2""F"",""C"")))"
    .Range("E2:E" & lr).Formula = "=IFERROR(S2,"""")"
    .Range("R2:R" & lr).Formula = "=IF('Raw Data'!B2="""","""",'Raw Data'!B2)"
    .Range("S2:S" & lr).Formula = "=IF('Raw Data'!G2="""","""",100*(EXP((17.625*D2)/(243+D2))/EXP((17.25*C2)/(243.04+C2))))"
End With
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,029
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

A couple of changes, as the formulas you provided contained typo errors

VBA Code:
Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Sheet2")
lr = Sheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row
With ws
    .Range("A2:A" & lr).Formula = "=IF('Raw Data'!F2&"" ""&'Raw Data'!A2="""","""",'Raw Data'!F2&"" ""&'Raw Data'!A2)"
    .Range("B2:B" & lr).Formula = "=IF(R2="""",01/01/1900,R2)"
    .Range("C2:C" & lr).Formula = "=IF('Raw Data'!G2="""","""", IF(AND('Raw Data'!G2>0.1,'Raw Data'!G2<900), CONVERT('Raw Data'!G2,""F"",""C"")))"
    .Range("D2:D" & lr).Formula = "=IF('Raw Data'!I2="""","""", IF(AND('Raw Data'!I2>0.1, 'Raw Data'!G2<900), CONVERT('Raw Data'!I2,""F"",""C"")))"
    .Range("E2:E" & lr).Formula = "=IFERROR(S2,"""")"
    .Range("R2:R" & lr).Formula = "=IF('Raw Data'!B2="""","""",'Raw Data'!B2)"
    .Range("S2:S" & lr).Formula = "=IF('Raw Data'!G2="""","""",100*(EXP((17.625*D2)/(243+D2))/EXP((17.25*C2)/(243.04+C2))))"
End With
End Sub
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
A couple of changes, as the formulas you provided contained typo errors

VBA Code:
Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Sheet2")
lr = Sheets("Raw Data").Cells(Rows.Count, "A").End(xlUp).Row
With ws
    .Range("A2:A" & lr).Formula = "=IF('Raw Data'!F2&"" ""&'Raw Data'!A2="""","""",'Raw Data'!F2&"" ""&'Raw Data'!A2)"
    .Range("B2:B" & lr).Formula = "=IF(R2="""",01/01/1900,R2)"
    .Range("C2:C" & lr).Formula = "=IF('Raw Data'!G2="""","""", IF(AND('Raw Data'!G2>0.1,'Raw Data'!G2<900), CONVERT('Raw Data'!G2,""F"",""C"")))"
    .Range("D2:D" & lr).Formula = "=IF('Raw Data'!I2="""","""", IF(AND('Raw Data'!I2>0.1, 'Raw Data'!G2<900), CONVERT('Raw Data'!I2,""F"",""C"")))"
    .Range("E2:E" & lr).Formula = "=IFERROR(S2,"""")"
    .Range("R2:R" & lr).Formula = "=IF('Raw Data'!B2="""","""",'Raw Data'!B2)"
    .Range("S2:S" & lr).Formula = "=IF('Raw Data'!G2="""","""",100*(EXP((17.625*D2)/(243+D2))/EXP((17.25*C2)/(243.04+C2))))"
End With
End Sub
I appreciate everything everyone has done. I'm going to work from home on this later tonight and I will update everyone tomorrow. I've found a few issues in my formulas and have amended them so the sheets I'm working on are a little simpler. I just realised that all my FALSE values were being calculated and again, skewing my data.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,029
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So, why not upload a SMALL sample workbook to dropbox from home ??
 

Yiew33

New Member
Joined
Nov 24, 2015
Messages
43
So, why not upload a SMALL sample workbook to dropbox from home ??
I can't email my raw data to myself due to our servers preventing outgoing mail to anyone other than @ourdomain, so when I say work from home, I meant like one or two formulas using made up information just to see if the functions work.

But, after finding what was wrong with my formulas whilst at work, and applying the information you've given me, I've managed to mostly get the macro up and running, which is fantastic. I haven't tried it on a large set of data yet, but for the small things it's working well.

This will significantly reduce the file size and has shown me how to apply this for future reference, even if I'm not too familiar with what everything means in the macro just yet.
 

Forum statistics

Threads
1,144,702
Messages
5,725,835
Members
422,645
Latest member
Victor9903

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top