Results 1 to 7 of 7

20080612, 16:53 #1
 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 userdefined formula in this context.
Help!

20080612, 17:00 #2
 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.

20080612, 17:21 #3
 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".

20080612, 18:19 #4
 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 userdefined 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).

20080613, 11:06 #5
 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.

20080613, 15:35 #6
 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.jkpads.com
Professional Office Developers Association

20080613, 17:57 #7
 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