+ Reply to Thread
Results 1 to 3 of 3

Array Formulas taking too long to calculate

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    Sacto
    MS-Off Ver
    Excel 2007
    Posts
    9

    Array Formulas taking too long to calculate

    Hi, I have a spreadsheet that I need to have calculate heads per half hour in a detailed manner. Attached has 2 sheets. The first one is "Shift Data", the secound one is "Headcount Calcs". On the Headcount Calcs sheet, i basically got it to do what I need it to do but it takes FOREVER to calculate and sometimes I have to re-start it. I've tried several different ways to calculated it and this is the most accurate way I can do it, unfortunately it seems to be longest too. I've read that sometimes VBA with massive use of array formulas might be better, but I'm not sure where I would start with that.

    So basically I'm asking if anyone knows a better formula or tricks to make it process faster. The example I give is only 1/5th(limited due to file size limitations) of what I need in production in terms of days included and columns with more details by Half Hour. The example runs faster but with 5x the data, it takes a very long time.

    Thanks for looking and for any help!HalfHour HeadCount Examplev2.xlsxHalfHour HeadCount Examplev2.xlsx

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Array Formulas taking too long to calculate

    I don't do database type work, so I'm probably not the best one to help you figure out the details.

    I haven't looked through the file thoroughly, but, as I look at your file, I see something very similar to what benishiryo and I discussed months ago http://www.excelforum.com/excel-form...41#post3111341
    Namely, I see a lot of duplication of effort. Example: Every single cell in columns C through G of headcounts is needing to locate which records have "english" in column 12. It seems to me that an obvious way to speed this up is to eliminate all this duplication of effort. My first suggestion might be to make separate sub tables based on the main table for English and Spanish (and other languages if present). This can be accomplished using lookup functions or filters or whatever you find easiest to work with. You might also consider if it will be worthwhile to further break up the main table into full-time, management, etc. so each cell is not checking for those sub categories.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    03-07-2013
    Location
    Sacto
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Array Formulas taking too long to calculate

    Thank you MrShorty, I did somewhat of an attempt to breakdown the groups but in the end it probably was the same thing though I eliminated the need to check the Language. I tried to set aside columns specifically for the subgroups and then do the calculations that way but in the end it still took just as long.

    Not sure how much you looked at the data, but I've tried countifs formulas and few other formulas without success. Technically they were successful in what it should do but the biggest issue is being able to correctly calculate fractional minutes into the heacdcount as not all shifts start or end on the half hour dots.

    I'm considering an attempt at a possible VBA script to sort of loop and build a day or week at a time, and once its done, to turn them into values then loop to the next day or week or something like that... but ultimately I'd love to just have to copy and paste updated data and the whole thing gets calculated.

    See attached my latest attempt with the sub-groupings.

    HalfHour HeadCount Examplev6.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Optimizing Formulas - taking too long to calculate large worksheet
    By lightsandsirens in forum Excel General
    Replies: 0
    Last Post: 08-21-2012, 10:38 AM
  2. [SOLVED] Array Formula taking too Long
    By gborja888 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2012, 03:41 AM
  3. Is there anyway to see why ScreenUpdating is taking so long?
    By foxguy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-11-2011, 04:17 AM
  4. Excel (2003) array formula taking too long
    By Krazy Kasper in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2008, 11:26 PM
  5. Array Formulas take waaaay too long...
    By belly0fdesire in forum Excel Formulas & Functions
    Replies: 39
    Last Post: 09-06-2005, 07:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1