Why is my Excel soooo slow...

dfalir

New Member
Joined
Mar 8, 2011
Messages
9
Guys hi, i need someone to help me with the following question please...

I have built an excel that has 9 worksheets. Out of these, 4 are coming directly from a mysql database. The other 6 worksheets, make some calculations, which are.

19 Vlookup functions (on different worksheets, on this excel only(
8 mid, left, right formulas in order to extract names, etc
4 Sumproduct formulas.

Every change or adition I make, my I7 @ 2,93 GHz seem unable to respond. By that i mean that if I make any change to any cell, the result will be displayed after 8-10 seconds. Why is taking so much time? What can i do to improve timings? My excel data, is not that much. The actual data from all worksheets must sum up to less 1000rows.

and one other question. if i have made a simple calculation. like price that includes vat, in one worksheet, and then i need to display the same value in another worksheet, is it faster to use vlookup to retrieve the value or is faster to do this simple calculation all over again?

Any other word of advice of how can i make this excel faster? I want this excel to work fast on an atom netbook, so I am thinking that if it takes 8 secs for any change on an i7 intel, how many ages will it take on netbook laptop?

Please help... :)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Show some examples of your VLOOKUP and SUMPRODUCT formulas.
 
Upvote 0
Glenn thank you very much for your interest, please any help is appreciated. I have spent over a week building this excel, and it is soooo slow..i am desperate for a solution.

Ok, to give you an idea (overview) of my excel, i have an online shop. In this excel, i directly copy the invoice i get from my supplier. The excel first breaks down the info into models, colours and prices, this is the first sheet. The second sheet, sorts alphabetically the unique products. Then there is a sheet that is retrieved from a joomla database with the sales, and another sheet that i mannually enter any sales not registered in the database. A third sheet adds the quantities of both the web sales and the manual sales. Hence the worksheet "analytics" has all the info in several columns. The average price bought, the current prices of items including VAT, prices excluding vat, stock (sales - buys), etc. The excel is not big as fasr as data it has less than 500 rows of items originating from invoices.

Now to your question.

This are the three commands that i use to seperate data from the "one line invoice"

1-line invoice example: <TABLE style="WIDTH: 463pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=617><COLGROUP><COL style="WIDTH: 463pt; mso-width-source: userset; mso-width-alt: 22564" width=617><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 463pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=617>2 TL140496 Caterina - Lady leather bag - Colour: Brown 0% 67,42 EUR 134,84 EUR</TD></TR></TBODY></TABLE>
The above line i split up with these commands
=IFERROR( (MID(C30;FIND(" ";C30;1)+1;(FIND(" ";C30;FIND(" ";C30;1)+1)-FIND(" ";C30;1))-1));"") - To get the code (TL140496)
also
=IFERROR(MID(C30;(FIND(" ";C30;(FIND(" ";C30;1)+1))+1);(FIND("-";C30;(FIND(" ";C30;(FIND(" ";C30;1))))+1)-FIND(" ";C30;(FIND(" ";C30;1)+1)))-2);"") - to get the name of the item, and so on.


Now regarding sumproduct take a few commands
=SUMPRODUCT(('TusOrders'!F:F=B8)*('TusOrders'!H:H=C8);'TusOrders'!G:G) - to some up the quantities

=SUMPRODUCT((('TusOrders'!E:E)=A2)*(('TusOrders'!H:H)=C2);'Tuscany Orders'!J:J)/SUMPRODUCT((('TusOrders'!E:E)=A2)*(('TusOrders'!H:H)=C2);'TusOrders'!G:G) - to find average price (this i did today, even without this command, excel does not improove speed)


calculating sales from websales worksheet is this: =SUMPRODUCT((DB_Sales!C:C=A2)*(DB_Sales!Q:Q=C2);DB_Sales!E:E)
calculating sales from manual import "sales" worksheet is this: =SUMPRODUCT((Sales!D:D=A2)*(Sales!F:F=C2);Sales!H:H)
Adding the 2 above gives me total sales.

Unique ITEMS:

In order to automatically find and sort all unique items (I do this from the invoice worksheet) i use these for formulas.
=IF('TusOrders'!D2="";"";IF(COUNTIF('TusOrders'!$D$2:D2;'TusOrders'!D2)>1;"";ROW()))

=COUNTIF($C$2:$C$350;"<="&C2)

=IF(ROW(A1)>COUNT(A:A);;INDEX('TusOrders'!D:D;SMALL(A:A;ROW(A1))))

=VLOOKUP(CELL("row";B2);$B$2:$C$350;2;FALSE)


any suggestions please?
 
Upvote 0
also fogot to say, my vlookups are pretty simple. a few axamples.

=IFERROR(IF(VLOOKUP((B24&D24);Analytics!D:M;1;FALSE)=B24&D24;"Product Found";"Error");"CHECK SPELLING!!!") (these are the most compicated type)

=VLOOKUP(B24&D24;Analytics!D:M;3;FALSE) these are the simple type
 
Upvote 0
Change all of your SUMPRODUCTs to use a limited range instead of entire columns. You mentioned that less than 1000 items were involved ... make your SUMPRODUCTS cover 1000 rows then.
 
Upvote 0
Glenn hi,

the problem with what you are saying is that i am afraid what will happen if i forget to update my sumproducts with the "appropriate" rows each time. So i found an article saying how to expand the rows dynamically but i could not understand it 100%. Could you please help me with this? the article i found is on the http://www.decisionmodels.com/optspeedk.htm

and explains how to drastically improve calcuation time from 3 hours to 14 secs... but i can not quite figure out the exact way that i should implement this on my excel... any help is appreciated, if you could explain it abit for me...

If not, i will do as you proposed before. Thank you!!! ::)
 
Upvote 0
i allready tried what you said! Thank you thank you ! very happy! blazing fast! :) I will try and sort out the dynamic thing i send you, but still even if i dont make it, your suggestion put a smile on my face!!! very good! :) :) :)
 
Upvote 0
I havent looked in detail at your sumproduct formulas, but it looks like you could use SUMIFS instead, I see you are on XL07 or later (because you're using IFERROR), so look into using this formula instead - its much faster than SUMPRODUCT
 
Upvote 0
1. Use SUMIFS rather than SUMPRODUCT.
2. Use INDEX/MATCH rather than VLOOKUP.
3. As Glenn said, don't refer to entire columns unnecessarily.
 
Upvote 0
Keep it simple like Glenn suggested:

Code:
=SUMPRODUCT(('TusOrders'!F1:F2000=B8)*('TusOrders'!H1:H2000=C8)*('TusOrders'!G1:G2000))

=SUMPRODUCT(('TusOrders'!E1:E2000=A2)*('TusOrders'!H1:H2000=C2)*('Tuscany Orders'!J1:J2000))/SUMPRODUCT(('TusOrders'!E1:E2000=A2)*('TusOrders'!H1:H2000=C2)*('TusOrders'!G1:G2000)) 

=SUMPRODUCT((DB_Sales!C1:C2000=A2)*(DB_Sales!Q1:Q2000=C2)*(DB_Sales!E1:E2000))

=SUMPRODUCT((Sales!D1:D2000=A2)*(Sales!F1:F2000=C2)*(Sales!H1:H2000))
 
Upvote 0

Forum statistics

Threads
1,218,740
Messages
6,144,215
Members
450,531
Latest member
avril18

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
Back
Top