Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have attached a mock data set that I am attempting to do a complex (for me) calculation. I am attempting to summarise thousands of rows of data, hopefully without intermediate formula's. Once again hoping that the lounge can bail me out..... Many Thanks in advance.

    [attachment=85861:Lounge.xls]
    Attached Files Attached Files

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

    =SUMPRODUCT(($A$6:$A$31=I6)*(LEFT($B$6:$B$31,4)=J$ 3)*($C$6:$C$31-$D$6:$D$31))

    and fill down. (It will be slow if the number of data rows is large)

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796865' date='08-Oct-2009 00:12'](It will be slow if the number of data rows is large)[/quote]

    Thankyou, Thankyou, Thankyou!!!

    I threw a bit of a strop at work today with a ridiculous workbook that was 70mb, a tab for each month of the year since Jan 09, about 4,000 data rows on each tab (updated daily), multiple unnecessary intermediate columns of formula's, dragged down to row 65,536.

    Now THAT, is slow!!!! I cleaned it up somewhat, reducing the file size to 30mb, this should improve it much further.

    What's the saying..... If we always do what we have always done, we will always get what we always got, or summit like that....



    One question: I have noticed in the past that SumProduct formula's can be very slow, and that every time the raw data columns are updated / altered, then every SumProduct formula is (slowly) re-evaluated. Do you have any tips for working with them, other than splitting into smaller data-sets?

    My thought was to have one raw data set for the year (approx 35,000 rows) with one daily summary, but If I do that, that would actually be worse, right?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could set calculation to manual, and only recalculate when you really need it.

    With large datasets, I'd prefer to use a database instead of Excel.

Posting Permissions

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