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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Show some examples of your VLOOKUP and SUMPRODUCT formulas.
 

dfalir

New Member
Joined
Mar 8, 2011
Messages
9
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

New Member
Joined
Mar 8, 2011
Messages
9
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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

New Member
Joined
Mar 8, 2011
Messages
9
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

New Member
Joined
Mar 8, 2011
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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_

Well-known Member
Joined
Nov 9, 2009
Messages
567
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))
 

Forum statistics

Threads
1,141,018
Messages
5,703,754
Members
421,313
Latest member
Mooncake1

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