Excel 2007 calculations crawling

[thart21]

I have created a worksheet with 3 dropdowns and several cells that are calculated based on the dropdowns. My formulas consist of INDEX/MATCH formulas in most of the cells. I originally referred to a column/row range in my formula like this:

=IFERROR(INDEX(Data!$N1:$N6500,MATCH(1,INDEX((Data!$B1:$B6500=$L$1)*(Data!$U1:$U6500=$P$1)*(Data!$A1:$A6500=C10),0),0),),"-")

Everything has been re-calculating quickly until I changed the references in the formula to
=IFERROR(INDEX(Data!$N:$N,MATCH(1,INDEX((Data!$B:$B=$L$1)*(Data!$U:$U=$P$1)*(Data!$A:$A=C10),0),0),),"-")

to avoid having to change the range when my data table got too large.

Suddenly now it is giving me the "Calculating (2 processors)" message and is taking about 5 minutes each time I select anything from the combo boxes which changes the data.

Could what I did above, changing the row reference to be wide open instead of limiting it to 6500 rows be the cause of this? I have a few SUMIFS that I have set up the same way and did not experience any performance issues with them.

Thanks!

Toni

 

[GlennUK]

.... Could what I did above, changing the row reference to be wide open instead of limiting it to 6500 rows be the cause of this?

yes, of course. That's a massive increase in processing.

.... I have a few SUMIFS that I have set up the same way and did not experience any performance issues with them.

Show your formulas and people here will be able to explain why such an increase in processing has not occurred in those cases.

 

[RoryA]

SUMIF(s) is optimised for this sort of thing - array multiplication is not. I would suggest you look into dynamic named ranges.

 

[thart21]

Thanks for the replies, I dove into learning about dynamic named ranges and it helped speed my formulas up a lot. I am still seeing the "Calculating (2 processors)" message and it is taking about 30 sec-1 min for my data to refresh after selecting a month in my combo box. Much better but would like to have it faster for my users.

Below are the formulas I am using throughout the worksheet. I have not created named ranges for some of them yet and, before I do, was hoping someone could tell me if it was really necessary if I specify a column/row range in my formulas rather than leaving them wide open.

=IFERROR(INDEX(TotalDmd,MATCH(1,INDEX((DIV=$L$1)*(FuturesMo=$P$1)*(DailyDate=C10),0),0),),"-")

=SUMIFS(PGI_Data[Open Qty],PGI_Data[Div],$L$1,PGI_Data[CCD Bus Mo Yr],$P$1,PGI_Data[SO Plan Gds Iss Dt],"<="&DATE(YEAR($M$1),MONTH($M$1)+1,0),PGI_Data[DailyDate],C10)

In 75 cells =IFERROR(INDEX(Data!$N1:$N500,MATCH(1,INDEX((DIV=$L$1)*(FuturesMo=$P$1)*(DailyDate=C10),0),0),),"-")

In 75 cells =SUMIFS(Rejects!$E$1:$E$200,Rejects!$B$1:$B$200,$L$1,Rejects!$D$1:$D$200,$M$1,Rejects!$A$1:$A$200,C10,Rejects!$C$1:$C$200,"Z001")

In 50 cells =IFERROR(INDEX(Data!$O1:$O5000,MATCH(1,INDEX((Data!$B:$B=$L$1)*(Data!$U:$U=$P$1)*(Data!$A:$A=C40),0),0),),"-")

Thanks for taking a look at this for me, appreciate it!

Toni

 

[GlennUK]

Just on quick inspecition, this formula should be changed:

=IFERROR(INDEX(Data!$O1:$O5000,MATCH(1,INDEX((Data!$B:$B=$L$1)*(Data!$U:$U=$P$1)*(Data!$A:$A=C40),0),0),),"-")

... the full column references should be changed to something smaller ... you are limited the INDEX to 5000 rows, so why are you searching entire columns?

 

[thart21]

Hi Glenn,

I do need my calculations to reference the full columns as the range will expand daily, I just hadn't created the named range for that particular field yet. I was still trying to determine what was causing the slowdown of my calculations. I have changed most of the formulas to reference my dynamic named ranges for my summed fields (different with each row on my spreadsheet, DIV, FuturesMo and DailyDate with no difference in calculation speed.

I still have a few more to update to dynamic named ranges but, if it doesn't increase my refresh speed, what are my other potential options for the culprit?

Thanks for your help!

 

[GlennUK]

I have changed most of the formulas to reference my dynamic named ranges for my summed fields (different with each row on my spreadsheet, DIV, FuturesMo and DailyDate with no difference in calculation speed.

If you have only changed most of the formulas, and not all of them, that may be your problem. I use FastExcel to profile workbook calculations, and spot bottlenecks ... can you purchase that ( according to the DecisionModels website it costs £44 or $79 or €53 per License ... most places that I've worked at have purchased it, at my suggestion )?

http://www.decisionmodels.com/fxlbuy.htm

 

[thart21]

Hi Glenn,

Thanks for the link, I will check into it. In the meantime I've been playing around with optimizing my named range formulas. I replaced using this formula in my named Range "DIV":

=OFFSET(Data!$B$1,0,0,COUNTA(Data!$B:$B),1)

With
=INDEX(Data!$B:$B, 1, 1):INDEX(Data!$B:$B, COUNT(Data!$B:$B), 1)

And it sped up my worksheet to almost be instantaneous when a selection is made in my combo box. Wow, amazing to watch the difference that a little formula change can make!
I then had the issue with blank cells and the range would not capture all the way down. I created another named range "Div_LastRow" which is:

=LOOKUP(2,1/(Data!$B$1:$B$5000<>""),ROW(Data!$B$1:$B$5000))

Then added that to my "DIV" named range

=INDEX(Data!$B:$B, 1, 1):INDEX(Data!$B:$B, Div_LastRow, 1)

Works great! If you see anything that could be improved upon or potential issues I could run into using this, I would appreciate your letting me know.

Thanks again!