Excel Calculating Problem

[dramnath1981]

Everyone must have come across this Calculating issue when excel is filled-up with formula.

But I am wondering is there a way to find out which spcific cells are causing this issue? This is because, not all the formula cells are problematic. There might be very few cells containing volatile formula for e.g. Today(). But its very tough to scan through 10000 cells with 100 columns. Any macro or simple solution will b of great help to everyone.

Kindly advice.

Regard, Ramnath

 

[Hui]

Ramnath

You haven't exactly told us what the problem is ?

I assume it is calculating slowly?

Can you please elaborate?
posting a sample file will also assist us assist you.

 

[dramnath1981]

Yes. I have a 48 MB file which has the customer portfolio details and runs up to 77000 rows with 43 columns and additional 20 formula columns and 11 sheets.

When ever I open this file, for every action, excel starts calculating and it really takes a decent 3 mins for every input.

I need to find out the exact formula or the column which is time consuming. I have uploaded a sample file which has the formula in it.

 

[Khalid NGO]

Hi,
I am sure Sir Hui will give you better feedback.

May be because of columns AX and AY (having same formulas) calculating external lookups.
Can you check the result after deleting both ?

Regards,

 

[Hui]

Columns AX & AY have the same formula as they are array entered
Have you tried using Non-Array formula instead?
in AX2:
=IFERROR(VLOOKUP(A2,'C:\Users\ Change to suit \Temp\BPM\Base Files\12.2014\[CB_CIF_MAST_20141130 (New).xlsx]OPS Banca MIS W_Status_201411'!$AN:$AP,2,FALSE),0)

in AY2:
=IFERROR(VLOOKUP(A2,'C:\Users\ Change to suit \Temp\BPM\Base Files\12.2014\[CB_CIF_MAST_20141130 (New).xlsx]OPS Banca MIS W_Status_201411'!$AN:$AP,3,FALSE),0)

Similarily in BL2, BM2
Don't reference full columns

BL2: =IFERROR(VLOOKUP(I2,$BZ1:$CB20000,2,FALSE),"")
BM2: =IFERROR(VLOOKUP(I2,$BZ1:$CB20000,3,FALSE),"")

Those are 50 times smaller than referencing the whole column

 

[dramnath1981]

Many thanks Hui & Khalid for your expert advise.

The calculating duration is now reduced to 45 seconds.

For the benefit of everyone, may be you can consider posting these tips about the probable time consuming formulas, which can be avoided.

 

[Hui]

The references in Ax and Ay2 should similiarly be changed

in AX2:
=IFERROR(VLOOKUP(A2,'C:\Users\ Change to suit \Temp\BPM\Base Files\12.2014\[CB_CIF_MAST_20141130 (New).xlsx]OPS Banca MIS W_Status_201411'!$AN1:$AP20000,2,FALSE),0)

in AY2:
=IFERROR(VLOOKUP(A2,'C:\Users\ Change to suit \Temp\BPM\Base Files\12.2014\[CB_CIF_MAST_20141130 (New).xlsx]OPS Banca MIS W_Status_201411'!$AN1:$AP20000,3,FALSE),0)

If you can reduce the values 20000 to 10000 you will also improve performance

If the data doesn't change, you may also want to consider copy/paste as values the Column AX and AY, that will stop Excel looking outside the existing workbook, which is probably the slowest part of the model