User defined functions that are array functions
 

I need to repeatly use some complicated array functions.
For example, this function which calculates conditional sums and only
returns a value if it is positive:
=IF(SUM(IF(From=$E3,IF(To=H$1,Cost,0),0))-SUM(IF(From=H$1,IF(To=$E3,Cost,0),0))<0,"",SUM(IF( From=$E3,IF(To=H$1,Cost,0),0))-SUM(IF(From=H$1,IF(To=$E3,Cost,0),0)))

Of course, after I enter this formula I have to hit Ctrl + Shift + Enter

Is there a way to convert this into a user defined function like:

Public Function ConditionalSum(Column1Values, Column2Values, Column3Values)

...
...
End Function


--
Richard

User defined functions that are array functions
 

A User Defined Function can return an array of values just an a normal
array formula worksheet function. The only thing to keep in mind is
the size and orientation of the array to be returned. See
http://www.cpearson.com/Excel/Return...ysFromVBA.aspx for example
code and usage considerations.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 22 Oct 2009 09:41:01 -0700, Richard
wrote:

I need to repeatly use some complicated array functions.
For example, this function which calculates conditional sums and only
returns a value if it is positive:
=IF(SUM(IF(From=$E3,IF(To=H$1,Cost,0),0))-SUM(IF(From=H$1,IF(To=$E3,Cost,0),0))<0,"",SUM(IF( From=$E3,IF(To=H$1,Cost,0),0))-SUM(IF(From=H$1,IF(To=$E3,Cost,0),0)))

Of course, after I enter this formula I have to hit Ctrl + Shift + Enter

Is there a way to convert this into a user defined function like:

Public Function ConditionalSum(Column1Values, Column2Values, Column3Values)

..
..
End Function