User defined functions that are array functions

R

Richard

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
 
C

Chip Pearson

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/ReturningArraysFromVBA.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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top