Why is my Excel soooo slow...

[dfalir]

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...

 

[GlennUK]

Show some examples of your VLOOKUP and SUMPRODUCT formulas.

 

[dfalir]

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?

 

[dfalir]

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

 

[GlennUK]

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.

 

[dfalir]

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!!! :

 

[dfalir]

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!

 

[baitmaster]

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

 

[RoryA]

1. Use SUMIFS rather than SUMPRODUCT.
2. Use INDEX/MATCH rather than VLOOKUP.
3. As Glenn said, don't refer to entire columns unnecessarily.

 

[snb_]

Keep it simple like Glenn suggested:

=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))