Array Formulas Limitatins - Could be PC memory size problem

[Spiros]

Hello,

I have developed a workbook that uses array formulas. These formulas examine 30K rows of data.
The sheet works if I limit the calculations to the first 1400 rows. If I drag these formulas down past row 1400
all previous calculations return as "zero". The sheet takes many seconds to calculate and save.

The following site states I could have a memory size issue.
https://support.microsoft.com/en-ca/kb/166342
Available memory
The Excel versions that are listed in the "Applies to" section do not impose a limit on the size of worksheet arrays.
Instead, you are limited only by the available memory on your computer.
Therefore, you can create very large arrays that contain hundreds of thousands of cells.

Other sites list methods to improve calculation performance when using array formulas.

I have spent considerable time developing this sheet. First creating a test workbook.
Once all was satisfactory I copied (hard values) into the test sheet, the 30K rows of data and dragged functional
formulas down to the last row. That's where the enjoy stopped.

Any suggestions on how to solve this problem would be much appreciated.

Regards,

Spiros

[Richard Buttrey]

Yes, array formulae are notoriously slow to calculate when large numbers of rows are involved.

Why not share your formula with us, preferably in the context of a cut down workbook (we don't need zillions of rows).
There may well be a more efficient non array formula or other functionality which will be more efficient.

[:) Sixthsense :)]

Or

Arrive the result with normal formula's by using Helper column(s)

[FDibbins]

There are often ways around needing to use ARRAY formulas, show us what you have and maybe we can come up with something

[Tony Valko]

Lots of tips wrt efficiency here:

http://decisionmodels.com/

[Spiros]

Thanks for showing interest in my problem.

Attached is the workbook I have developed with pseudo data.
(So, I could not find the "Attach File" button).
It is limited to 46 rows. This workbook started with an array formula
from John Walkenbach's Excel formulas book using an array formula
"{=SMALL(IF(FITTING_No.=$A2,ROW(FITTING_No.))}"
expanded to "{=IFERROR(INDIRECT(ADDRESS(SMALL(IF(FITTING_No.=$A2,ROW(FITTING_No.)),H$1),COLUMN($E:$E),4,1)),0)}
to lookup the row number(s) of "Fitting #" where there is more than
one instance in order to examine column data to the right and flag
comparison errors. Necessary when looking at 30K rows of data.

Regards,

Spiros

[shg]

No attachment.

You are using full-column references with a volatile formula that requires sorting 30,000 rows in each instance. That is brutally inefficient.

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Pete_UK]

Here's a more complete description of the steps to attach a workbook:

Click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and you will see the filename listed on screen. Click on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

[Spiros]

Thanks "newdoverman" Unfortunately the "attach file" feature is not working.

When I click the icon an explorer window or similar does not open to allow
me to select my file to attach. My
IT dept. may be responsible here unless someone has any suggestions.

Regards,

Spiros

[Pete_UK]

You may have the Enhanced Uploader set to on in your UserCP settings, and this can cause problems with some browsers. Click on UserCP at the top of the screen, and then on My Account/General Settings on the left, and scroll down (2nd bottom) and change it to OFF, then Save Changes. I have mine set to this.

Then try it again.

Hope this helps.

Pete

[Spiros]

Thanks Pete_UK. Unfortunately no enjoy as yet.

Spiros

[Spiros]

Attached is my sample workbook.

Not sure what you mean by "Remember to desensitize the data".

Column "T" demonstrates the objective.

Regards,

Spiros

[Spiros]

I should mention; Thanks Pete_UK, your more complete explanation for attaching files got it done.

And "shg" you wrote:
"You are using full-column references with a volatile formula that requires sorting 30,000 rows in each instance. That is brutally inefficient."

I tested my file with constants, removed full column references with no better performance.
I also removed the column "T" and all its conditional formatting etc... No joy there either.

Regards,

Spiros

[shg]

Why not just autofilter col A?

[shg]

Alternatively -- if you can sort by col A and use a helper column on the other sheet, you can use a non-volatile formula maybe 1000 times faster.

[Spiros]

I do not believe "Autofilter" has the power or easy to do what the user (not me) is looking for.

There are 30K rows of data. The user is tasked with looking for entry errors etc.
Specifically looking at the "Fitting #" in column A. Finding all instance of this unique number,
then examining data entered into columns E & F. Column T tells the user visually if there is a
need to look into the data further. We decided to limit the "Fitting #" to a maximum of six instance.
It is expected there will be no more than this.

To put this into real world context, the entries are items moving on and off a boat.
Weight in kg is tracked, part of centre of gravity that will eventually effect the boat as whole (stability).
If all went well a Fitting # would only have two (2) entries, an ON and an OFF, and weight would be equal.
In reality an item may be removed in parts under the same Fitting # and therefore have more than two entries.
But the total weight may or may not equal. This as an example must be flagged and examined, which is the ultimate
goal of column "T". Column "V" list some but not all possible errors that may occur.

Once column "T" produces the required result then "Autofilter" can be used to minimize the data and sort out errors.
Well, this is the approach I have devised. I am sure there are better ways and would love to be shown the light
by anyone following this post.

Also I developed a second sheet named: "6 Point Look".
The idea here is for the user to look down column "T" on the sheet named "MT - COPIED DATA" and for speed and
convenience copy and paste the Fitting # into Cell B3 of the sheet named "6 Point Look".

Hope this helps explain my goal.

Regards,

Spiros

[Spiros]

Hello All,

Thank you for showing interest in my problem.
It has been a great education into Array Formulas.
Specifically their power and limitations.

After many hours of research, some of it directed from this forum (thank you)
I understand Array Formulas more and have been able to use them
where appropriate to solve my problem.

I was able to alter my method and way of thinking once I came across the following website
that offered an alternative.
http://excelbyjoe.com/3-ways-to-deal...okup-in-excel/

With this formula, =A2&COUNTIF($A$2:A2,A2) and concept I was able to replace the
Array Formula initially used ("{=SMALL(IF(FITTING_No.=$A2,ROW(FITTING_No.))}")
to handle Multiple Instances (Occurrences) in a List.

By removing the Array Formula (which was calculating 6 X 30K times per Fitting No.) and using
A2&COUNTIF($A$2:A2,A2) I was able to reduce the work load and get the result I was seeking.

I have attached a reduced desensitized data file showing my work for anyone who is interested.
The working file has 30K rows of data and growing. I processed the file formula by formula,
copy and pasting the hard values until I had everything I needed. Only the formulas in
Columns “S &T” remain in the working file to flag Error Conditions. Not the supreme solution,
but I have the result and we can move forward. The file processes without delay.

If someone is willing to look at this deeply and offer a higher level solution I for one would
appreciate the opportunity to see how the pros get it done.

For now I will mark this post as “SOLVED”.


Regards,



Spiros