How To Speed Up Large Matrix Array Formulas
Dec 31, 2013
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.
View 3 Replies
ADVERTISEMENT
Apr 20, 2004
I have been lurking around for past month learning lots from MrExcel's wonderful web site. One of the many things I learned was how to improve my spreadsheets with Array Formulas, but today I ran into a problem on a new spreadsheet I'm building for work.
Here's the problem: I have 39 coworkers. For each coworker, I have 14 Array Formulas using SUMPRODUCT command with up to 5 "conditions", similar to this example:
{=SUMPRODUCT((user=$A$5)*(task=AO$3)*(DateChecked>0)*((Error="Error Removed")+(Error="Error Converted to an FYI")))}
Each condition such as "user" and "task" is a static named range of 5000 cells. This spreadsheet will hold one week's worth of my coworkers' work. This past week they have processed about 2500 items. To be safe, I doubled this number to determine the static named range size.
For each worker I have 56 columns (one for each possible task which a coworker can process).
So for each coworker, there will be 14 * 56 = 784 Array Formulas.
Currently my spreadsheet only has a single coworker defined, so I only have 784 Array Formulas, but it takes 35 seconds at 100% CPU Utilization when I press F9 (Calculate all formulas). Right now, I am running this on my Home PC (a 400 MHz PII PC with 256 Megs of RAM, OS is Win2000 at SP4 maint level and Excel 2002), but it is equally slow at work (1.7 GHz Celeron with 256 MB of RAM running Win2K SP4 and Excel 2K).
I haven't tested yet, but even if I assume a linear progression, with 39 coworkers I am thinking it is possible the amount of time for Excel to recalculate all the formulas will be 39 times longer than it is currently. This will be close to 22 minutes. That is a long time to wait! It will be even worse if my testing shows the amount of time Excel takes to evaluate the array formulas is exponential instead of linear...
...784*39 = 30,576 formulas...
View 9 Replies
View Related
Oct 14, 2008
I I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
View 2 Replies
View Related
Aug 22, 2008
Using VBA, I wish to work out the inverse matrix of a large matrix (100*100), but keep getting the # Num! Error. I am using the minverse function. I have defined variable as "variant", does this give me the same possiblities in terms of number size as the variable "Double"?
View 9 Replies
View Related
Jun 12, 2009
This is my first post on this forum and I am an Excel novice. I have a worksheet with lots of blank cells and I need to shift all the cells with data all the way to the left (to column A). I cannot get the sort or filter functions to do this for me, and I've never used macros but it appears they might be useful for my problem. I'd like to find an automatic process to move the data left rather than drag and drop hundreds of times. I'm attaching the complete xls file for your viewing.
View 5 Replies
View Related
Nov 20, 2008
I currently have a macro set up to delete rows if a certain user selected value is not found in a certain column. It works fine if the sheet isn't overly large but the problem is we have some spreadsheets with 25,000+ rows and it takes time to loop through - I'm not sure if there is even a way to make it faster.
Currently it works by looping backwards on the sheet checking each value in the cell versus an array containing the user selected values. If a match isnt found it deletes this row.
'y = long value representing row
'x = counter for each item in user selected array
'wsSheet = worksheet we are using
'rowLast = last row on spreadsheet
'arrSearch() = string containing user selected items
i have screenupdating/calculations/events turned off - i just didnt know if there was a faster way of looping through
i just thought about instead of doing an array maybe joining the array into 1 string and using a "like" comparison check to see if a match is found - would save me from having to constantly loop x * y amount of times - not sure if this would work or not. ill post back if it doesn't
View 9 Replies
View Related
Feb 16, 2009
I have the following used formula to count how many occurances there are of a particular piece of text in a column:
=SUM(IF(range="text",1,0))
e.g. {=SUM(IF(K6:K16="H",1,0))}
I have repeated this formula for an adjacent column
e.g. {=SUM(IF(L6:L16="M",1,0))}
I need to find a way of counting number of occurances in these two columns (K and L) when text "H" and "M" are present in the same row.
To further complicate matters (well for me anyway) the text "H" and "M" care chosen from a list of options in the K and L columns.
What i ultimatly want to do is create a matrix to compare how many times all possble occurances of the various options listed in both columns are present in the same row in the spreadsheet.
View 2 Replies
View Related
Mar 8, 2013
I'm trying to write a formula that will return the sum of multiple cells within a larger matrix, based on a number that is manually inputted into a separate table. As you can see in the top below, I have data that is grouped by both "Zone" and by individual "Stations". The Zones/Stations on the y-axis are "Start" points, while the Zones/Stations on the x-axis are "Finish" points. What I'd like to be able to do is input a Zone ID number into the bottom table (Shown as "1" below) and have the formula either return the greatest sum of "Finish" numbers. In this example, the greatest sum is 12, which is all numbers going from Station 1 and 2 in Zone 1 to Stations 1/2 in Zone 2 (3+2+4+3). In the bottom table, you'll see the "Greatest Finish" as "2", which is Zone 2. And the total from this sum is 12, as described previously. If there is a way to return either the "Total #" or "Greatest Finish" zone.
The actual matrix I am working with is much larger (150x150), and not all zones have the same amount of stations. I'm assuming this might complicate things a bit.
Finish
Zone 1
Zone 1
Zone 2
[Code]...
View 6 Replies
View Related
Oct 28, 2006
I have this example array:
A1
B1
C1
D1
A2
B2
B3
C3
D4
C5
D5
E5
...along with this example table:
.12345
A
B
C
D
E
In my table, I want to notate every occurence of A=1,A=2,B=1, etc; by placing an "X" in the correct cell. So, the table should look like this:
.12345
AXX
BXXX
CXXX
DXXX
EX
I cannot figure out the formula. I've tried this:
=If(VLOOKUP($L6,$I$6:$J$15,2,False)=M$5,"X"," ")
....but it seems to only return the first value found, and not all occurences.
View 3 Replies
View Related
Feb 27, 2008
I'm trying to use VBA to do some matrix processing. I have successfully done matrix processing in the spreadsheet, but I'm looking to port the logic into VBA to more easily reuse central formulas and reduce the amount of code in the spreadsheet.
The function shown below works. I had to hack around the variable declarations to eliminate processing/compile errors but it seems to function (no pun intended).
While debugging I noticed that this function would seem to get called 4 times. I do have other modules running, although I don't think any other modules are affecting this.
After some input from shg, I updated the variable declarations, but I'm getting an "Overflow" error reported. Sometimes (not always) a divide by zero also pops up. I do have the Msgbox for errors at the end, I haven't tried removing that part to see if the error reporting vanishes. I'm not sure 'hiding' error alerts is the best thing to do anyway...
View 8 Replies
View Related
Mar 7, 2008
I have tried to find information on this subject, but it seems diifficult to find exactly what I am looking for. I have provided a test example of the operation that I would like to acheive. The first example (Method 1) works well enough but would require defining more detailed functions beyond the scope of average. This would probably be ok, but more importantly, I would like to source particular arrays in this manner for other tasks beyond averages and even standard deviation and am not quite sure how this can be done. In an attempt to describe the situation, I have provided a second example (Method 2). In this example, I am trying to take the average of the jth column array to create an array of averages. I am not sure if I am incorrectly setting up the matrix, if the functions don't perform this way in the matrix, or if this is simply not possible.
Sub test()
a = 5
b = 5
Redim X(1 To a, 1 To b) As Double
'Assumes there is a matrix of values [a x b] drawn from worksheet
For j = 1 To b
For i = 1 To a
X(i, j) = Cells(i, j)
Next
Next
'----METHOD 1---------------------------------------------
'I know this way works using average as an example, but...
Redim AverageX(1 To a, 1 To b) As Double.......................
View 4 Replies
View Related
Jun 6, 2013
i was wondering if there was a way i can combine these two formulas into one large one
=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))
=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))
View 2 Replies
View Related
Jun 20, 2014
So in Cell K12 there is a rather large formula (I condensed it for this example).
This formula is to average out the respective cells in column J. For each cell there is a possibility of 9 different entries. NRT, N/O, 1,2,3,4,5,6,7
So based on the formula in K12 I need Cell J12 to Display the answer.
if any one of the Cells referenced in the formula are NRT then I need it to Display NRT. Which the Formula does now.
The problem comes in when a Cell is N/O (Not Observed)
How do I get the formula to Ignore N/O currently I have it set up to recognize N/O as 0. But excel averages 0 in and it affects the answer.
I know in a simple formula I can add <>0 to the formula to ignore zeros. How to write it into a bigger formula such as in K12.
For example the current numbers in Cells J17:J25 should average 5 however the formula averages it as 1 because it calculates N/O as 0.
View 14 Replies
View Related
Jul 12, 2014
I am accustomed to using filters to find a lot of my information in large datasets.
However, now I am trying to use formulas to return specific values. For simplicity's sake, I have included a sample below with a couple types of scenarios I am looking to solve through the use of formulas. Would this involve sub-arrays perhaps?
sabinfire1.xlsx
Excel questions.docx
View 5 Replies
View Related
Mar 17, 2009
i have a spreadsheet with a certain data (rate of return) associated to a specific day of a year. (ex. 8-dec.-08 0.99865), and i have to calculate the rate of return (r) for every month, which is done by applying a formula (GEOMEAN) to all the rate of returns for the days of a month (ex. GEOMEAN(r[01 dec.]:r[31 dec.])).
i don't have any problems with that part. the hard part is that i have every single trading day from jan 00 to dec 08 (2000+ days), and i do not want to manually select the ranges. also, the dates do not include weekends (trading days per year = 252) which means that i cant (a) automatically determine a range or (b) automatically associate a number of days to a month (ex. oct 08 doesn't have the same amount of days as oct 07, because of the way week-ends and holidays are arranged..)
View 5 Replies
View Related
Nov 20, 2013
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
View 1 Replies
View Related
Jul 16, 2012
how to return the top 5 results in a set of data.
In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.
View 1 Replies
View Related
Dec 21, 2012
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?
View 9 Replies
View Related
Jan 8, 2010
I'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:
View 4 Replies
View Related
Jun 8, 2007
Column C contains the names of real-estate brokers.
Column E contains the colors of the homes for sale.
Column Q contains the values of the homes.
I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.
I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.
View 9 Replies
View Related
Jun 20, 2007
Hi again, I am trying to find the easiest way to repeatedly search a large matrix (300,400) for a particular value. I have used two for loops in the past but its starting to get complicated now. Is there a workaround using application.worksheets.something that I could get a true of false reply? Someone may suggest that I create a function that replys back. I am new to VB and I am not sure how to send a matrix of integers to a function (syntax).
View 8 Replies
View Related
Dec 19, 2007
could provide me a loop function in VBA for these calcultions.
Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1
These are the calculations for E1-G1.
The process must be repeated till E50-G50
So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2
View 12 Replies
View Related
Feb 28, 2012
I have four named ranges (Segment, Keyword, Impressions and Dropdown) and I would like to create a formula-based ranking of keywords by impressions and clicks. Using the following array formula, I am able to return the correct values for impressions or clicks:
{=LARGE(IF(Segment=DropDown,Impressions),$H7)} where $H7 is the number ranking 1, 2, 3 etc.
My question is what array formula could be used to find which row in the array returned that number and then pulls the data from the same row in the other named ranges?
Essentially find row of {=LARGE(IF(Segment=DropDown,Impressions),$H7)} but return Keyword and Clicks on that row.
Other Notes: I cannot use pivot tables and some values might be the same which would make Vlookups not accurate for duplicate values.
Link to an example document to clarify this. [URL] .......
View 9 Replies
View Related
Aug 26, 2007
I have a data chart on 200 people with overlapping membership in 20+ groups, represeted as binary (1=member, 0=notmember), for example: ...
View 9 Replies
View Related
Nov 28, 2008
Attached is the file where I want to derive Min Date of seq A and asset Type Char.
SeqAsset TypeStDateEndDate
AChar21-11-200828-11-2008
BEnv22-11-200829-11-2008
CProp30-10-200830-11-2008
AChar03-11-200801-12-2008
AChar04-11-200801-12-2008
AChar05-11-200801-12-2008
AChar06-11-200801-12-2008
AChar07-11-200801-12-2008
I want to find the Minimum Date of Seq "A" and of Asset type "Char". I used following Array Formulas but showing the correct answer 30-10-2008MIN(IF(A2:A9="A",C2:C9="Char"),(D2:D9)) But Istead of 03-11-2008 it's showing 30-10-2008 date of seq C and of asset type Prop
View 2 Replies
View Related
Oct 14, 2005
I typed in the word array into Excel Help and found this item
"About array formulas and how to enter them"
I am trying to duplicate the first example and cannot
Ex: =Average(if(C5:C14="Europe",D5:D14))
I tried something similar to this (diff cell ref) and I get #VALUE
Do I need to check off an addin or something?
View 9 Replies
View Related
Sep 12, 2012
Do array formulas work if the array is across sheets instead of across columns or rows? I'm getting a #Ref! error when I try to use an "across sheets" array.
I'm doing this:
=sum(if('Sheet 1:Sheet 2'!A1=1,'Sheet 1:Sheet 2'!A2,0))
With a CTRL + SHIFT + ENTER return.
View 5 Replies
View Related
Apr 18, 2006
I am having little luck creating an array to work with 3 critieria. Here is what I am trying to do: In the attached spreadsheet I am trying to show a total for each "Global Process" based on if "Reason Cancelled" and a date greater than or equal to March 1st, 2006. Here is what I currently have for the array formula: note: this is an Excel formula not VBA. I couldnt find the right code
=SUM(( 'Raw Data(Added_Cancelled)'!$D$2:$D114="Fulfillment")*('Raw Data(Added_Cancelled)'!$I$2:$I$150="Other")*('Raw Data(Added_Cancelled)'!$H$2:$H114>="2006-03-01 00:00:00"))
View 7 Replies
View Related
May 14, 2009
Does anyone know how to activate a block of different array formulas at once??
Example:
N7:Q80 has a total of 296 Array Cells. Each has a unique formula & I cannot just drag to fill these nor can I activate all at once.
In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.
btw, Why do I have to press F2? Is that only in Excel 2007? I googled pretty extensively & don't see an option how to only press CTRL+SHIFT+ENTER. It would be nice not to have to press F2 everytime.
View 14 Replies
View Related
Nov 16, 2013
I would like to master the dreaded array formulas. Any Excel based accounting consolidation tool or other consolidation tool out there that I could adapt to consolidate group accounts on a monthly basis.
View 5 Replies
View Related
