Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Array formulas in VBA (Excel 2003)

    Hi,

    I have some statistics to calculate on a range of data which could be done with an array formula. I have used an array formula in some of the cells where the conditions are of medium complexity. Now I have do do a calculation which is really rather complicated.

    On one sheet I have all my data in a table. The rows represent different contracts our company has (about 1000 rows) and the columns provide information about them e.g. status, value, date etc.
    In another sheet I am calculating some stats. I have a table of departments against months. I want to calculate the a contract value win/loss ratio for each department in each month. I can do that in three stages:

    1) value of contracts for department X won in month M = the sum of the values of all the contracts where the department is X, the status is either A, B or C and the date is in the month M
    2) value of contracts for department X lost in month M = the sum of the values of all the contracts where the department is X, the status is Z and the date is in the month M
    3) win / loss ratio = value of contracts won in month M / (value of contracts won in month M + value of contracts lost in month M )

    This would be one mega array formula and probably more than 255 characters. I wanted to do a VBA user defined function instead, presumably passing in the department and month as arguments, but I don't particularly want to loop through all 1000 contracts for each cell calculation in the stats table - this would take forever.

    Is there any quick way of getting the array formula into a VB function so at least the formula looks neat and tidy in the spreadsheet? I have read about cell.ArrayFormula but I don't know how to use this in a user-defined formula in this context.

    Help!

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

    Re: Array formulas in VBA (Excel 2003)

    Could you post a small sample workbook that shows what your data look like? Replace real data with dummy data.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Array formulas in VBA (Excel 2003)

    The data is on the first sheet and the table to be completed is the final one on the last sheet (Chart Data)

    I should have said that in my description of the calculations above, "department" is "cost centre" in the first sheet and statuses A, B & C are actually "F - Preferred", "G - Invitation to Proceed" & "H - Award of Contract" in reality
    Status X is "X - Lost".
    Attached Files Attached Files

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

    Re: Array formulas in VBA (Excel 2003)

    The ArrayFormula property can be used to set the array formula of a cell using VBA. It can't be used to calculate array formulas in VBA.
    A user-defined function would probably be slow. I'd prefer to use formulas and lots of intermediate results (which can be hidden if you prefer). See the attached version (I've used SUMPRODUCT instead of array formulas).
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Array formulas in VBA (Excel 2003)

    Hi Hans - thanks very much for the solution and the clarification! See now I am feeling guilty that you've done my work for me.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array formulas in VBA (Excel 2003)

    If you'd add a helper formula column that figures out the criteria you mentioned I suspect you could do this easily with a pivot table.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Array formulas in VBA (Excel 2003)

    ahh see, pivot tables is something I haven't ever tried! hmm

Posting Permissions

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