MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Speeding up my calculations


Posted by Richard Larocque on January 08, 2002 4:31 PM

This morning someone on this board helped me out with a sort of Rank(If) array formula that works extremely well. 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. Any suggestions to speed up the process?


Posted by paul Johnson on January 08, 2002 5:23 PM

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

Posted by Richard Larocque on January 08, 2002 5:40 PM

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!

Posted by Paul Johnson on January 08, 2002 5:45 PM

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

Posted by Richard Larocque on January 08, 2002 6:25 PM

Re: How about

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

Posted by Jacob on January 08, 2002 6:46 PM

Re: How about

Hi

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

Jacob

Posted by Aladin Akyurek on January 08, 2002 7:01 PM

> 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

Posted by Richard Larocque on January 08, 2002 8:03 PM

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

Posted by Richard Larocque on January 08, 2002 8:33 PM

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.

Posted by Aladin Akyurek on January 08, 2002 8:41 PM

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

Posted by Aladin Akyurek on January 08, 2002 8:41 PM

also the addendum above.

Aladin

Posted by Aladin Akyurek on January 08, 2002 8:51 PM

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

Posted by Aladin Akyurek on January 08, 2002 8:55 PM

Search... Re: How about

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

Aladin

====

Posted by Richard Larocque on January 08, 2002 9:40 PM

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!

Posted by Aladin Akyurek on January 09, 2002 3:40 AM

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

==========

Posted by Aladin Akyurek on January 09, 2002 8:05 AM

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

===========

Posted by Richard Larocque on January 09, 2002 8:57 AM

Re: Recap

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

Thanks!