Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    very slow calculation (Excel 2003)

    I have a workbook with 5 separate sheets used to populate 5 listboxes on a different sheet. The largest of these tables is 155 rows. Each table has columns "Product Name, Price, Qty, Description". The calculations are very simple: Qty = SUM(range), and GrandTotal = SUMPRODUCT(range, ">0"). This is done once for each of the five lists/sheets.

    The main (input) worksheet is mostly cell references to these totals on these back-end sheets (which are hidden). This same sheet is the one with the five listboxes (the user interface to this calculator). I have code that detects a double-click for each of these five listboxes that pops up a InputBox asking for desired quantity. This quantity is then written to the respective worksheet (one per listbox) which is what causes the re-calc.

    I know it's something to do with calculation because when I set it to Manual, it immediately responds, however my grand totals aren't updated, of course. If I then do F9 to recalc, the workbook will sit for about one minute!!

    Are the SUMPRODUCTS the problem? It's not like I'm doing super hairy formulas, they're simple multplications of small numbers (such as 3 x $79,000).

    Any clues? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: very slow calculation (Excel 2003)

    SUMPRODUCT is basically an array formula (although you don't have to enter it as such), and array formulas can be costly in time. I don't understand =SUMPRODUCT(range,">0") though, shouldn't that be =SUMIF(range,">0") ? See the thread <!post=COUNTIF and SUMIF slow recalculation (Excel 2003),410736>COUNTIF and SUMIF slow recalculation (Excel 2003)<!/post>.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: very slow calculation (Excel 2003)

    Maybe I'm using the wrong formula (overkill). My product tables are like this:

    Product | Quantity | Price | Description

    I read this table to populate the list boxes and when a user clicks an entry, it displays the corresponding description text in a label field. On a double-click of an entry it prompts them for a quantity. This quantity value is then written back to the corresponding table (on a hidden sheet). On that cell change, there are formulas that update the total of all quantities, and total price of all non-zero quantities. If the following table is in A1:B3:

    <pre>ProdAA, 4, $100, textAA
    ProdBB, 0, $250, textBB
    ProdCC, 10, $200, textCC</pre>


    So I want to always keep a running total of the products purchased which is the non-zero quantity rows. I used SUMPRODUCT(A1:A3*B1:B3), I'd get $2400 as the current running total of purchased products.

    I do the same on the total quantity selected (4 + 10 in this example). SUMIF(A1:B3, ">0") is the cumulative quantity.

    I haven't yet looked at your link on SUMPRODUCT and SUMIF but will do that now. I'd hate to see how long it took if I had 1000's of rows, instead of just 150 <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15>

    Deb

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: very slow calculation (Excel 2003)

    If you just want a summary table, a Pivot table may be your best bet.

    Steve

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: very slow calculation (Excel 2003)

    Attached is an edit version of my multi-list box application that has these tables which are really slow. I replaced any data that is confidential with dummy data. The sheets are visible and unprotected and the code is not protected either. I had to remove about 100 rows to get the .zip file to fit as an attachment so hopefully this doesn't *fix* the problem. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    The main page (EquipSelect) is the only one the user will ever see and the others are back-ends for the 5 list boxes and a calculate page that has rules on it for determining the discounts, etc. for each type of equipment.

    Can you review this and see where I can improve the calcs so it's not so slow? The problem occurs when you double-click any of the list boxes and enter a new quantity at the prompt. If you do this a few times, Excel will slow down so much that you can't select a cell and continue doing something else for almost a minute. I have code in the workbook_calculate event that shows me what 3 sheets are affected by changing the quantities.

    Any ideas are much appreciate as I'm 99% done with this application and this last thing needs to be fixed before I can distirbute it.

    Thnx, Deb <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: very slow calculation (Excel 2003)

    When I test in Excel 2002, the workbook reacts quickly most of the time, but every now and then, it seems to freeze. The mouse pointer won't change shape any more and clicks won't register. But using the mouse wheel will restore things to normal immediately, each time. I'm afraid I don't understand what causes this, I don't see anything suspicious in the code.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: very slow calculation (Excel 2003)

    How many is a "few". I don't see a sluggishness in the calcs.

    Steve

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: very slow calculation (Excel 2003)

    A few?? ummm, I don't remember exactly but I'm sure I got rid of probably 100 rows total (some from each of the 5 product sheets),

    And here I thought I was 99% done <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> It's always that last 1% that kills ya, right? <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Deb

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: very slow calculation (Excel 2003)

    Yes that's what I see too, mouse pointer appears normal but I can't click a cell and if I do Alt+F11 I can hit break to stop it of course. How about if I replace the formulas with simpler ones (the 5 back-end table sheets I'm talking about)? Or what about just doing it in code. I know VBA would be slower but it can't be any slower than it already is now. I could replace the logic I have on the sheets to calculate discounts, etc. with code too and just write the answers into the cells (and turn calc off). What a pain!! <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

    I was hoping to get this app done today as it works well and people like it. I can't release it being so slow that they think Excel is locked up. This is for my job and we all have Excel 2003 so I no choice there.

    Maybe it's the inputbox prompt I use or the listboxes that really mess it up. I can experiment with not prompting the user and have them enter the quantity in a cell (hey, I'm grasping at anything right now <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    I can't think of another way to implement this application that's as easy to use and as compact. I'm open to suggestions though.

    Thnx, Deb

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: very slow calculation (Excel 2003)

    I am not entirely sure what the "issue" is but the mouse pointer seems to hang until you CLICK on the object after you dbl-click.

    Once you click on it, the other items can be dbl-clicked.

    perhaps this will give someone else some ideas of troubleshooting, I am not sure where to go with it... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Steve

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: very slow calculation (Excel 2003)

    Hey good catch!!! That gave me an idea.... What I ended up doing is selecting a cell at the end of each listbox's x_Dblclick() event code. That way the listbox object isn't selected and so far that seems to fix it (even w/o changing calc mode to Manual and back to Auto). It's kind of like that pesky property of a button 'TakeFocusOnClick' which defaults to False but always causes problems unless it's True.

    I'll try it more but so far I can change it more than 10 times and it doesn't hang <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> WHEW!! You saved my weekend!!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Deb

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: very slow calculation (Excel 2003)

    Glad I could a little bit to help...

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: very slow calculation (Excel 2003)

    Although you seem to have solved the problem already, I want to point out to you that in the SUMPRODUCT function the arrays need to be separated by a "," not a "*" as you show. Though the "*" seems to work it can give erroneous results as I found out by chance recently. This might also have something to do with the speed.

    Regards, Teunis

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: very slow calculation (Excel 2003)

    Can you give an example of SUMPRODUCT giving incorrect results with * ?

  15. #15
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: very slow calculation (Excel 2003)

    Sorry Hans, I cannot because I discarded the workbook.

    Regards, Teun

Page 1 of 2 12 LastLast

Posting Permissions

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