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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,447
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top