Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Why is my Excel soooo slow...

This is a discussion on Why is my Excel soooo slow... within the Excel Questions forums, part of the Question Forums category; Guys hi, i need someone to help me with the following question please... I have built an excel that has ...

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    9

    Default Why is my Excel soooo slow...

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

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: Why is my Excel soooo slow...

    Show some examples of your VLOOKUP and SUMPRODUCT formulas.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    9

    Default Re: Why is my Excel soooo slow...

    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:
    2 TL140496 Caterina - Lady leather bag - Colour: Brown 0% 67,42 EUR 134,84 EUR

    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?

  4. #4
    New Member
    Join Date
    Mar 2011
    Posts
    9

    Default Re: Why is my Excel soooo slow...

    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

  5. #5
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: Why is my Excel soooo slow...

    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.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  6. #6
    New Member
    Join Date
    Mar 2011
    Posts
    9

    Default Re: Why is my Excel soooo slow...

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

  7. #7
    New Member
    Join Date
    Mar 2011
    Posts
    9

    Default Re: Why is my Excel soooo slow...

    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!

  8. #8
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    1,123

    Default Re: Why is my Excel soooo slow...

    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
    Baitmaster G
    wha'ever, I do whad I wan'

    Excel 2010 (work), 2010 (more work), 2007 on Win 7 and 2010 on Win 8 (home), 2003 (a dim and distant memory)

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,094

    Default Re: Why is my Excel soooo slow...

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

  10. #10
    Board Regular
    Join Date
    Nov 2009
    Posts
    488

    Default Re: Why is my Excel soooo slow...

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com