reference a function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Brehm - 16 Mar 2006 07:32 GMT

Can I do something like this?

A B
1 =F(A1)
2 =F(A2)
3 =F(A3)
4 etc
5
6
7
8
9
10

and define a function in a cell called F, say maybe =sin(X)? This would give
me the ability to plot any function I can put in a cell.

Reply to this Message

Ardus Petus - 16 Mar 2006 08:59 GMT

You will have to calulate the various functions in separate columns, then
use INDIRECT to reference the desired column.
See example: http://cjoint.com/?dqi70Wzv3V

HTH
--
AP

> Can I do something like this?
>
[quoted text clipped - 12 lines]
> and define a function in a cell called F, say maybe =sin(X)? This would give
> me the ability to plot any function I can put in a cell.

Reply to this Message

Herbert Seidenberg - 16 Mar 2006 18:19 GMT

=CHOOSE(my_func,SIN(table),COS(table),TAN(table))
Select which function you want by setting
a cell named my_func to 1, 2 or 3 (max is 29).

Reply to this Message

Bill Brehm - 17 Mar 2006 05:28 GMT

What do you mean by 'table' here? is it referring to Ardus Petus'
suggestion?

I was trying to look for a way to put a perhaps complicated function in only
one cell rather than in every cell in the range covered by the range of
arguments. I know I can do it with VB but looking for a way to do it on the
spreadsheet only.

Could array formulas be of any help?

> =CHOOSE(my_func,SIN(table),COS(table),TAN(table))
> Select which function you want by setting
> a cell named my_func to 1, 2 or 3 (max is 29).

Reply to this Message

Herbert Seidenberg - 17 Mar 2006 07:18 GMT

Assuming your data looks like this:
table    results
0.00    0.00
0.15    0.15
0.31    0.30
0.46    0.44
... ...
5.98    -0.30
6.13    -0.15
6.28    0.00

my_func
1

Table is the named range of numbers from 0.00 to 6.28
Select these numbers and
Insert > Name > Define > Names in Workbook > table
In the same manner, give the cell with the 1 the name my_func
Fill the <results> column with the formula given in the previous post.
The column <results> will now display the sine terms.
You can now plot the first column (radians) vs the second (x y plot).
To display the cosine, type in 2 in the my_func cell.
Now let's add an arbitrary fourth function, a quadratic.
Add Quad to the formula:
=CHOOSE(my_func,SIN(table),COS(table),TAN(table),Quad)
Add this name to > Names in Workbook > Quad
Refers to =0.58*table^2-1.78*table+2.34
Type 4 into my_func and the quadratic curve will appear in your graph.

Reply to this Message

Harlan Grove - 17 Mar 2006 06:11 GMT

Bill Brehm wrote...

>Can I do something like this?
>
[quoted text clipped - 3 lines]
>3 =F(A3)
>4 etc

...

>and define a function in a cell called F, say maybe =sin(X)? This would give
>me the ability to plot any function I can put in a cell.

The closest would be a udf like

Function F(a As Variant) As Variant
Dim fcn As String
Application.Volatile True
fcn = Evaluate("_F")
fcn = Replace(fcn, "$$", CStr(a))
F = Evaluate(fcn)
End Function

where _F would be a defined name referring to the actual function you
wanted to use, and $$ would be the token representing the argument to
F(). Note: you can't have both a udf named F and a defined name named F.

Reply to this Message