Clever By Value Question

[Daken]

Hello out there.

Thanks in advance for the assistance. Basically what I am
trying to figure out is a way to use a string in a cell as
the function in a formula.

Let me explain.

A B C
1 10 average =B1 & "(A1:A4)"
2 15 median =B2 & "(A1:A4)"
3 20 stdev =B3 & "(A1:A4)"
4 25 sum =B4 & "(A1:A4)"

Where I want to use the word "average" in my C column as
the input for the formula. Make sense? I know I can do
this through code, but I was hoping to do it with some
sort of byval function, or something along those lines.
Thanks in advance for any help.

Daken

 

[JE McGimpsey]

One way:

Can't do it via worksheet functions, but can use a UDF:

Public Function Evaluator(sFunction As String, _
sRange As String) As Variant
On Error Resume Next
Evaluator = Application.Evaluate("=" & sFunction & sRange)
If Err Then Evaluator = CVErr(xlErrValue)
On Error GoTo 0
End Function


Call as

=Evaluator(B1, "(A1:A4)")

 

[Frank Kabel]

Hi
you may use the following user defined function:
Public Function eval_it(eval_str)
eval_it = Application.Evaluate(eval_str)
End Function

use is in your example like
=eval_it(B1 & "(A1:A4)")

 

[AlfD]

Hi!

Do I understand you have 4 values (in col B):
you want to find the average, median, s.d. and sum and to have tha
displayed in col C as Average=17.5, Median = and so on.

If so, you could put the formulae in like this:

In C1 put ="Average = " & average(A1:A4)
In C2, 3, & 4, likewise using the formulae for median etc.

Al