I have a Intel i7 4770k cpu, (sometimes overclocked) and RAM of up to 32GB.
I find it hard to believe that Excel gives errors such as "Low system memory..." when users apply many array formulas, or use entire ranges (ie. A:A).
Even if the syntax works, it takes some time to load/execute. Why is that?
My system running with Intel P4 / 2.67 GHZ with 1 GB RAM which is very old comparing to your system configuration.
From my point of view it depends on many factors, like
The complication of your Array Formula
Applied on how many cells in a single instance?
Whether it posses any volatile functions?
If the Array Formula is referred to do the calculation on entire column ranges then surely it needs some time to process everything in the single cell.
So we need lot more details before giving some other alternate methods
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
short answer...yes
long answer...keep reading
http://office.microsoft.com/en-us/ex...010342495.aspx
http://msdn.microsoft.com/en-us/libr.../ff700514.aspx
ive found when i see memory error messages its usually corrupted file or too many volatile functions
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks for the quick reply.
I can't imagine how complex my formulas are.
I have an spreadsheet containing all my inventory, a ledger and an products sorting sheet, which uses an array forumla in order to get the row number from the ledger.
Here's a sample:
={IF(ROWS(C$994:C995)<=$C$992,SMALL(IF(Cash!$C$9:$C$1000000=$C$991,Cash),ROWS(S$994:S995)),"")}
By retreiving that row number, I retreive all the relevant data using index (ledger!Z:Z, row number from that array)....(ledger!AG:AG, row number from that array) x probably over 500 cells.
I also allow iterative calculation too, probably over 100 cells. That is likely to increase.
Last edited by jonnyyyl; 07-30-2014 at 01:43 AM.
If possible, post your most complicated array formula of that workbook to know what is happening at your end
FYI. I have upgraded to Office 2010 because I read from various forums that 2010 version tries to merge the hardware and software performance better than previous versions.
Yes, 2007 / 2010 version of calculation is much speeder than 2003
It's not very complex by all means. It is just that I repeat the same type of calcuation over and over. Intuitively, it is quite simple: retreive row number from massively long ledger, based on the product type, and then use that row number to get key data by using index().Originally Posted by :) Sixthsense :)
If possible, post your most complicated array formula of that workbook to know what is happening at your end
I retreive row number from the ledger using this:
Row 1 = {=IF(ROWS(C$8:C8)<=$C$6,SMALL(IF(Cash!$C$9:$C$1000000=$C$5,Cash),ROWS(S$8:S8)),"")}
There is more than one entry, so that array drags down to get all the row numbers of entries related to say, product A (ie. cell S8 = row 1 of the ledger, cell S9 = row 28 of ledger , cell S10 = row 58 of ledger etc etc etc...)
With the row data in place, I use this to retrieve all the relevant data
Relevant column 1 =IF($S8="","", INDEX(Cash!$B:$B,$S8))
Relevant column 2 =IF($S8="","", INDEX(Cash!$C:$C,$S8))
Relevant column 3 =IF($S8="","", INDEX(Cash!$D:$D,$S8))
Relevant column 4 =IF($S8="","", INDEX(Cash!$D:$D,$S8))
I drag that entire bit down to say 50 down in order to account for future entries:
so at Row 50 it is still this:
Relevant column 1 =IF($S50="","", INDEX(Cash!$B:$B,$S50))
Relevant column 2 =IF($S50="","", INDEX(Cash!$C:$C,$S50))
Relevant column 3 =IF($S50="","", INDEX(Cash!$D:$D,$S50))
Relevant column 4 =IF($S50="","", INDEX(Cash!$D:$D,$S50)).
I got over 60 products, this process repeats 60+ times, all the way up to row 1000+.
Last edited by jonnyyyl; 07-30-2014 at 02:01 AM.
This part is giving more work to excel...
Cash!$C$9:$C$1000000=$C$991
Limit this range 1000000 to your last used data row for speeder performance.
Or
Assign a helper column to do the IF Calculation of the data on each cells like this...
IF(Cash!$C9=$C$991,Cash!$D9,"")
Drag it down...
Now your current array formula is converted into normal formula which does not require any array processing...
=IF(ROWS(C$8:C8)<=$C$6,SMALL(IF_Formula_Column/Range_Reference_Here,ROWS(S$8:S8)),"")
I think I can do that quickly. Off the top of my head, I think I find the cell based by on last entry. So it can be something like:
Cash!$C$9:$C$1000=$C$991, where C1000 can be quickly found.
I don't entirely follow this part.
Would it be alright if I can post a sample and you could be so kind to show me a way?
Last edited by jonnyyyl; 07-30-2014 at 02:38 AM.
Pls see attached.
You can read the red colored comments and yellow cells, I am not sure what I have done wrong....
Much appreciated
Ah my bad. did something stupid in the first version. v2 should be the correct one.
Last edited by jonnyyyl; 07-30-2014 at 04:04 AM.
Sorry for the delay, since I have been assigned for some tasks in my work place.
Go through the attached file in which I done major changes in your formula which can do the calculation much speeder than your current method.
C6 you put an extra +1 in columns to give you C instead of B
Formula:
Please Login or Register to view this content.
also for your E6
Formula:Please Login or Register to view this content.
is probably simpler....also...works if you have dates the wrong way round..which might not be the right thing though for a ledger =\
put in text because it changes to date automatically
method i used is slightly different to sixth using named range
Formula:Please Login or Register to view this content.
not sure if it is slower or faster than sixth's method though...
might be a bit slower than sixth method i think due to extra volatile named
but there is no helper column
Last edited by humdingaling; 07-30-2014 at 04:42 AM.
Thanks!
Tiny question:
How do I modify it so that it shows every product table. This only works if I am trying to look at one product because everything is tied to Sort!C3.
Usually what happens is that I create these tables and put them altogether in the All worksheet
C6 you put an extra +1 in columns to give you C instead of B
Formula:Please Login or Register to view this content.
also for your E6
Formula:Please Login or Register to view this content.
is probably simpler....also...works if you have dates the wrong way round..which might not be the right thing though for a ledger =\
put in text because it changes to date automatically
method i used is slightly different to sixth using named range
Formula:Please Login or Register to view this content.
not sure if it is slower or faster than sixth's method though...
might be a bit slower than sixth method i think due to extra volatile named
but there is no helper column
Much appreciated for the help that.
I created the C6 in the cash worksheet hoping that all future references will rely on what C6 returns, so that it is automatic and dynamic in that sense. I aim to make indirect(Cash!c6) the end of the range for all the yellow filled cells in v2, or at least use it as a reference to which last row to use in a range.
ie
This month, the last cell is C15, so all the countifs, match, index ranges will use cash!C9:indirect(Cash!c6) [which is C15, or a method that recognizes the correct row number].
Next month, the last cell used is C20, such changes will be accounted for.
The Sort worksheet is actually a form that I have made so that everytime there is a new product, I create a macro that makes a copy of this sheet, enter the product name and then cut+paste it in the separate sheet (all in the v3 attachment).
I like sixth's method, because it avoids arrays. However, it is all locked in one cell. This is my fault, for not fully explaining what is happening.
I hope to use the C6 month, which sixth suggested, because it reduces calculation range as well as avoid using arrays.
Last edited by jonnyyyl; 07-30-2014 at 05:05 AM.
You can convert it as Non Volatile by passing it in Index formula like this...might be a bit slower than sixth method i think due to extra volatile namedFormula:Please Login or Register to view this content.
=Cash!C9:INDEX(Cash!C:C,COUNTA(Cash!$C$9:$C$99999)+8)
Why/how does making the MaxCash name help improve efficiency and reduce load?
Do you happen to any thoughts on making the range dynamic and avoid arrays?
named range doesnt necessary make your sheet more efficient...its what makes the name range that does
less volatile formulas = more efficient spreadsheet
Took up sixth's suggestion to on maxcash
updated your overview sheet with new formula
you just need to reset row formula every section
Last edited by humdingaling; 07-31-2014 at 11:36 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks