How to Speed Up Large Matrix Array Formulas

[claborn3]

Hello all, first time poster here, so please go easy on me...

Background: I am trying to calculate a matrix of array formulas that is roughly 365 x 137, or about 50,000 cells being calculated from a range of roughly 12,000 x 137. There are multiple if statements within the array formula, and then those 50,000 cells are referenced to another, but smaller matrix of array formulas (25 x 137). Each of these matrix sets is for one year, and there are three years that need calculated.

Problem: When calculating the 50,000 cells it takes roughly 2.5 hours to complete all calculations. So, for 3 years it will take roughly one work day of tying up my computer to just compile data that will then require several days of calculation/manipulation.

Question(s): Is there a way to speed this up to a reasonable amount of time (I'd even take 30 minutes at this point)? Or, is Excel simply the wrong tool to be using for this amount of data? Do I need a better machine to run these calculations? I am currently using a Dell XT3 with 2.5 Ghz i5 quad core processor.

Thanks in advance and please let me know if there is anything else that might help in answering these questions.

[Pepe Le Mokko]

Hi
without seeing some of your data and formula, it's difficult to say.
Could you post a sample sheet on the forum ?

[claborn3]

Here is the formula that is from the 365 x 137 matrix:

MAX(IF(B$3=STORE_LIST_2013,IF($A4=CALENDAR_DATE_2013,HHOUR_TRANS_DATA_2013,""),))

B3 is store name, and A4 is the date.

Range "STORE_LIST_2013" is 137 cells, and range "CALENDAR_DATE_2013" is about 12,000 cells.

[MrShorty]

Whenever I see these kind of questions, I am reminded of this discussion between benishiryo and I http://www.excelforum.com/excel-form...ml#post3111341 What I observed in his examples is that there is a significant duplication of effort for each incidence of the desired function. It looks like that will be the case in your example here, too. Each incidence of that function will need to evaluate both the A4=calendar data 2013 array and the b3=storelist2013 array. And, when you have 20000 incidences of the function, that is a significant duplication of effort.

I know it is not a popular suggestion (for some reason, even with 1E6 rows and 1E4 columns, we want to use as few cells as possible), but my suggestion would be to use helper columns or blocks of helper cells to reduce this duplication of effort. Build a separate matrix as a subset of the raw data matrix that will show store B3 for calendar date A4 or some other way to shrink extract the desired data before calculating the max.