excel too slow with calculations

IraAxa

Board Regular
Joined
Apr 23, 2013
Messages
115
Hi there,

i tried to look for the solutions online regarding too slow excel calculations, and i applied all the advices regarding the formula usage, hence my excel once i hit F9 takes for ever to calculate. we are not talking about too much data - a bit over 5000, and the formulas it includes are:

=IF(ISNA(INDEX(Sheet!F:F,MATCH(1,INDEX((Sheet!A:A=Sheet1!A8)*(Sheet!C:C=Date2),),FALSE))),"",INDEX(Sheet!F:F,MATCH(1,INDEX((Sheet!A:A=Sheet1!A8)*(Sheet!C:C=Date2),),FALSE)))

=VLOOKUP(A3,Sheet!A:X,2,0)

=IFERROR((F3-H3)/H3,"--")

there are 2 tabs - Sheet & Sheet1

The Sheet tab pulls data from SharePoint - no calculations involved here - there is Refresh Connection involved , and The Sheet1 uses the calculations from that Sheet. It starts calculating once the Date is selected, but it takes forever.

TiA
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ghrain22

Active Member
Joined
Jan 9, 2014
Messages
473
A problem i see that could be causing an issue is you're looking at entire rows. ie. A:A, C:C, F:F, A:X

Excel may be using ALL of the rows in those columns slowing you down(especailly in the case of the vlookup). Try limiting them down. I'm assuming the number of rows either changes or is incosistent. Therefore I would suggest making some dynamic named ranges.
 

IraAxa

Board Regular
Joined
Apr 23, 2013
Messages
115
Great, you saved my day - it workes perfectly; i should have fixed amount data - but just in case i included few blank cells just in case:)

thanks a lot:)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top