Speeding up my calculations

You could switch to manual calc - to only calc when you need to. The only other option is eliminate unnecessary formulas, or a bigger pc.

PJ

My PC is big enough and I do have the setting of my Calc. on Manual. I admit there are a lot of formulas in my file, but all are required. I am stumped!

How about

Can you move of the worksheets to another workbook and link them ?

That way if the calcs are done in a sequence - you can only calculate the necessary sequence. saving on cpu time.

workbook 1
For instance forecast widgets

workbook 2
determine cost to make widget

Workbook 3
Forecast x Cost

Workbook 4
Chart the forecast and costs

Re: How about

You gave me an idea! I'll try that. Thanks a lot!

Re: How about

Hi

What are some of the calcs maybe we can smoothe them over or come up with an easier way.

Jacob

> This morning someone on this board helped me out with a sort of Rank(If) array formula that works extremely well.

It was me the culprit. :)

> The problem however, is that the column that the formula is ranking has about 9500 rows. It takes about 45 minutes to complete the calculations. It's a pain even opening my file because of the long wait times.

Richard, welcome to the world of array and/or SUMPRODUCT formulas. With huge number of records as you have (I didn't this when I proposed you formulas for "LargeIF" and "RankIf"

Any suggestions to speed up the process?

Yes. Eliminate/Not use those "charming" formulas:

Here are my alternatives that should be efficient in time, but not in memory/space.

A1:C11 houses your sample data including labels:

{"Industry","rating","Stock";
"a",9,"q";
"a",7,"w";
"a",5,"e";
"b",3,"r";
"b",1,"t";
"b",8,"y";
"b",6,"u";
"c",4,"i";
"c",2,"o";
"d",3,"p"}

I'll assume that the sample is sorted on column A.

In D1 enter: Start [ just a label ]

In E1 enter: End [ again just a label ]

In D2 enter: =IF(A2=A1,E1,ROW())

In E2 enter: =IF(A2=A1,F1,ROW()+COUNTIF(A:A,A2))

In F1 enter: Symbol [ a label that you used yourself when posting the LargeIf question ]

In F2 enter: =IF(MAX(OFFSET(IF(A2=A1,A1,A2),0,1,F2-E2,1))=B2,C2,"")

Note 1. This formula replaces the costly SUMPRODUCT formula for "LargeIf."

In G1 enter: Ranking [ just a label ]

In G2 enter: =RANK(B2,OFFSET(IF(A2=A1,A1,A2),0,1,F2-E2,1))+COUNTIF($A$2:A2,A2)-1

Note 2. This formula replaces the array formula for "RankIf."

Now select D2:G2 and give a dubble click on the little black square of the cell G2 in order to copy down them as far as required.

I'd like to hear how these performs in terms of time.

Regards,

Aladin

Ahh! Much better. Thanks again Aladin. And thanks to everyone who responded.

Aladin! I mispoke. The formula for F2 is a cyclical formula. In F1, does it matter what I put? What a doofus I am for mispeaking.

Addendum...

is about the "RankIf" part. It begins thus after the first part somewhere in the middle. I discovered a flaw in the former non-array design, whence the addendum.

Now select D2:F2 and give a dubble click on the little black square of the cell F2 in order to copy down them as far as required.

Addendum for correction start here, (I hope I'm not confusing you).

In G2 enter: =IF(A3=A2,H2,ADDRESS(ROW(),1)) [or just $A$2 as a constant, which indicates the start cell of the sample ]

In G3 enter: = =IF(A3=A2,G2,ADDRESS(ROW(),1))

Dubble click on the little black square of the cell G3 in order to copy down the formula as far as needed.

In H1 enter: Ranking [ just a label ]

In H2 enter: =RANK(B2,OFFSET(INDIRECT(G2),0,1,E2-D2,1))

Notice that this formula will assign equal ranks to ties.

Dubble click on the little black square of the cell H2 in order to copy down the formula as far as needed.

Note 2. This formula replaces the array formula for "RankIf."

I'd like still to hear how these perform in terms of time.

Regards,

Aladin

also the addendum above.

Aladin

Re: How about

Paul --

Interesting suggestion.

Another possibility for managing array and/or SUMPRODUCT formulas is that you first compute thru, leave the first cell in which they are intact, replace the rest of the cells by Copy then Paste Special > Values. Whenever a recalc need, give a dubble click on the black square of the first cell. Don't know if it's any good in real-life of huge number of records as Richard has.

Aladin

Search... Re: How about

for "Rank IF" & "Large(If)" to find the formulas.

Aladin

====

Re: Addendum...

Aladin! When I enter the formula in F2, I get a circular reference! I also get gibberish as cell results. See below:
START END Symbol RANKING RANKING
2 5 0 0 0
5 5 0 0 0
2 5 0 0 0
5 9 0 $A$5 3
9 5 0 $A$5 0
2 5 0 $A$5 0
2 5 0 $A$5 0
9 11 0 $A$9 1
11 5 0 $A$9 0
11 12 0 $A$11 1
I copied and pasted your formulas directly. Nothins is working. Life is futile!

Re: Addendum...

That doesn't look good. I'm sending you a workbook, which might be easier to use than copying & pasting from the board.

Aladin

==========

Recap

It appears I should have applied the same logic for "LargeIf" that I used for "RankIf" in Addendum that specifies the parameters/args for the OFFSET function, whence the recap that follows:

A1:C12 houses the following sample data:

{"Industry","rating","Stock";"a",1,"q";"a",3,"w";"a",5,"e";"b",7,"r";"b",9,"t";"b",2,"y";"b",4,"u";"c",6,"i";"c",8,"o";"d",7,"p";"d",4,"x"}

In D1:H1 enter:

{"Start","End",0,"symbol","ranking"}

Note that 0 stands for an empty cell.

In D2 enter: =IF(A2=A1,D1,ROW())

In E2 enter: =IF(A2=A1,E1,ROW()+COUNTIF(A:A,A2))

In F2 enter: =ADDRESS(ROW(),1)

In F3 enter: =IF(A3=A2,F2,ADDRESS(ROW(),1)) [ copy down this as far as needed ]

In G2 enter: =IF(MAX(OFFSET(INDIRECT(F2),0,1,E2-D2,1))=B2,C2,"")

In H2 enter: =RANK(B2,OFFSET(INDIRECT(F2),0,1,E2-D2,1))

Select D2:E2 and doubble click on the black square of E2 (in order to copy down).

Select G2:H2 and doubble click on the black square of H2 (in order to copy down).

D1:H12 will be showing the following:

{"Start","End",0,"symbol","ranking";2,5,"$A$2","",3;2,5,"$A$2","",2;2,5,"$A$2","e",1;5,9,"$A$5","",2;5,9,"$A$5","t",1;5,9,"$A$5","",4;5,9,"$A$5","",3;9,11,"$A$9","",2;9,11,"$A$9","o",1;11,13,"$A$11","p",1;11,13,"$A$11","",2}

Corresponding costly SUMPRODUCT and array formulas (Richard has a huge set of records) are to be found, along with required computations at

13624.html

and

13658a.html

I already know (off-line) how the non-array set of formulas perform. I bet Richard will report on that.

Aladin

===========

Re: Recap

Yep! Aladin is a genius. Everything is working fine.

Thanks!