Anyway to make Big file size and slow file small and faster

**sa02000** · 03-26-2009, 07:02 AM

I am using the formula below in one of the excel sheet. Basically it takes two tables (or sheets) and matches 3 criteria and multiplies the two values. Problem is I have to do this for about 100 columns and 30000 rows. So thats total of 3 million formulae that are calculated anytime I make any change and makes the file size huge. Does anyone have a better solution for doing this same thing that would make it faster and smaller file size?

='[File_v1.xls]Page1-1'!E5*SUMPRODUCT(ISNUMBER(MATCH(Ratios!$A$3:$A$62,$D4,0))*ISNUMBER(MATCH(Ratios!$B$1:$K$1,$E4,0))*ISNUMBER(MATCH(Ratios!$B$2:$K$2,$B4,0)),Ratios!$B$3:$K$62)

Thank you,
Jay

**nomel** · 03-26-2009, 04:22 PM

You could turn off 'auto calcutate' by going to the Tools -> Options -> Calculation menu. Set calculation to manual. This way, it will only update all of the calculations when you press F9.

For your matching, are you looking at the same values for each row?

If you are using the same match value, then rather than performing a search with the match command for every cell, put this in some other cell, then place a reference to that cell in it's place.

Please Login or Register  to view this content.

So, calculate once, reuse the answer for all. This won't work if the range or value you're searching changes.

Anyway to make Big file size and slow file small and faster

LinkBack

Thread Tools

Rate This Thread

Display

Anyway to make Big file size and slow file small and faster

Re: Anyway to make Big file size and slow file small and faster

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions