#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0

    Question Possible Alternative to Array Formula in VBA or Excel


    Currently, I am having an issue using an Array Formula in Excel 2010. I have a table that currently has about 2100 rows, but will grow over time to roughly 5 - 6 times that size.

    In one of the columns of this table, I have written an Array Formula that does exactly what I need it to, but takes well over 30 minutes to calculate (even just for the 2100 rows). For my current environment, this is unacceptable, however, I cannot come up with an alternate formula to get the information I require.

    Even the "Evaluate" function in VBA takes an exorbitant amount of time to run.

    Here is an example of my data, and what I need to return (sorry about the mess, but I couldn't figure out how to make the forum keep my whitespace):

    ID........PerDay......Date.......NumofDays
    =========================
    S224.........3.........1-Feb............2
    S224.........3.........1-Feb............2
    S224.........3.........1-Feb............2
    S224.........4.........2-Feb............2
    S224.........4.........2-Feb............2
    S224.........4.........2-Feb............2
    S224.........4.........2-Feb............2
    S224.........1.........3-Feb............2
    S224.........6.........4-Feb............2
    S224.........6.........4-Feb............2
    S224.........6.........4-Feb............2
    S224.........6.........4-Feb............2
    S224.........6.........4-Feb............2
    S224.........6.........4-Feb............2

    My formula is in the (NumofDays) column, and is supposed to return the number of days a particular 'ID' had more than 3 "PerDay" entries. Basically, if ID 'S224' had 4 entries on Feb 2nd, and 6 entries on Feb 4th, but only 3 on the 1st and 1 on the 3rd, then the formula should return 2.
    Here is the Array Formula in the NumofDays column:

    =SUM(IF([@ID]=[ID], IF((COUNTIFS([ID], [@ID], [Date], [Date])) <= 3, 0, 1/(COUNTIFS([ID], [@ID], [Date], [Date]))), 0))

    When I hold CTRL+Shift and press Enter, my formula becomes an array formula, and gives me the correct answer.

    This works fine on the above table, but my actual table (the one with over 2100 rows), is still calculating after about an hour now.

    If anyone can help with this, I would appreaciate it greatly. And if any clarifcation is needed, I am more than happy to accomodate.

    Thank you in advance,

    --Chris D.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    My experience with Excel is very limited. One of the reasons I don't use it is the slowness of the calculation process, which may be a result of the inefficient storage methodology. Might I suggest using an Access Database for your data. The calculation process will be more efficient, and you can always import the results back into Excel.

    J.A. Coutts
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    18
    Rep Power
    0
    I guess that it is doing a database access for each line

    There is no "within the workbook" formula that will take more than a few seconds for 12k rows (unless your pc is steam powered! - mine is 9 years old)

    Import your data to the spreadsheet
    Process it within the spreadsheet
    export the answers if necessary
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    5
    Rep Power
    0
    Originally Posted by GGriggs
    I guess that it is doing a database access for each line

    There is no "within the workbook" formula that will take more than a few seconds for 12k rows (unless your pc is steam powered! - mine is 9 years old)

    Import your data to the spreadsheet
    Process it within the spreadsheet
    export the answers if necessary
    It is not doing database access for each row. I already imported the data to a table, and am attempting to process the data within the sheet using an Array Formula in only one column.

    Bottom line, you are probably correct that no single "within the workbook" formula should take more than a few seconds, but this Array Formula is processing each of the 2100 rows, 2100 times. If you copy my test data from my original post into an excel table, and use the formula I provided, you can evaluate through it, and see how it works.

    That is why I am seeking an alternate way of doing this.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    1
    Rep Power
    0

    Idea to shorten calculations


    Originally Posted by Night1505
    Currently, I am having an issue using an Array Formula in Excel 2010. I have a table that currently has about 2100 rows, but will grow over time to roughly 5 - 6 times that size.
    ...
    Here is the Array Formula in the NumofDays column:

    =SUM(IF([@ID]=[ID], IF((COUNTIFS([ID], [@ID], [Date], [Date])) <= 3, 0, 1/(COUNTIFS([ID], [@ID], [Date], [Date]))), 0))
    ...
    --Chris D.
    the formula is pretty complicated as is, so I propose:
    1) make the formula only calculate if the current ID differs from the previous id, you can do such a thing like this: in d2 correct the formula to =if(a2<>a1, SUM(IF([@ID]=[ID], IF((COUNTIFS([ID], [@ID], [Date], [Date])) <= 3, 0, 1/(COUNTIFS([ID], [@ID], [Date], [Date]))), 0)) ,d1) , assuming that the NumOfDays formula is in column D.
    2) use intermediate columns to store your results so that your formula will not have to recalculate everything from scratch all the times. Then hide these extra columns to keep the view you like etc
    3) Create a VBA function to do the math instead of Excel, then enter the entire column D as an array to avoid multiple calculations (though doing so will mean you can only expand it but cannot diminish it anymore)

    I suggest you try 1) first and then 2), since 3) will be pretty advanced and need formula enabled workbooks.

    cheers

IMN logo majestic logo threadwatch logo seochat tools logo