Can an Excel Function return multiple values?

[MigrantFirmWorker]

I am using a Function to calculate a variety of parameters from a set of data in a spreadsheet. I know I can pass a single value back to the calling cell through the return value. Is it possible to send additional values back to other cells? I tried passing cells in as arguments, but I have been unable to set their values from the function.

-chris

 

[SkipVought]

Hi,
A function returns a single value.

However you could modify your function to a Sub and could act on a range. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Functions in a worksheet can only return a value to ONE cell. They cannot affect any change on the worksheet other than to their own cell and they cannot affect any kind of formatting change Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SteveBausch]

Show us the code, as it currently is, and show us a modified copy. Of course, your modified copy won't work, you will have used psuedocode to indicate what you expect.

 

[Zathras]

Chris, it sounds like an example of the sort of function you are looking for might be to convert cartesian coordinates to polar coordinates. You could use two separate functions like this:

A1: 'X-coord
B1: 'Y-coord
C1: 'Theta
D1: 'r
A2: 312
B2: 521
C2: =PolarAngle(A1,B1)
D2: =PolarDistance(A1,B1)

Or you could use just one function with a third parameter to indicate which value to return like this:

C2: =PolarCoord(A1,B1,1)
D2: =PolarCoord(A1,B1,2)

If you have a very time-intensive calculation that takes many input values and generates many output values and are concerned about performance, then the parameterized version of a single function is the way to go.

The thing to do is save the input and output values in global variables and test whether the solution has already been found for the current set of input values. If so, just select the appropriate output value and return it. If not, do a full calculation and capture the values for the next call. Then return the wanted value.