Create function based on header name

[Defakto]

Hi, I have a little problem with Excel, that I am not able to solve.
Let's say I have a table of numbers:

1

2

3

4

5

<tbody>
</tbody>

and I have a table with function names:

sum

average

min

max

count

<tbody>
</tbody>

Now, I want to create a second row below those function names, that would take the numbers in the first table and perform the function in the second table, without me actually typing each specific function out. I want to use the function names in the table as a reference to automatically create the function.
I tried something like: =A7&"(A1:A5)" where A7 refers to the sum function and the range A1:A5 refers to the numbers. I got a value error.

Thank you in advance for your answers.

 

[John_w]

You could try this EVALUATE function trick - https://www.vertex42.com/ExcelArticles/evaluate-function.html

In cell A8 put the formula =A7&"(A1:A5)". The result is the text sum(A1:A5). Next, define the name SUMresult and in the Refers To field enter =EVALUATE(A8). Finally in cell A9 enter the formula =SUMresult and the result is 15.

You'll have to repeat this for each function name, defining a different name for each function.

The other, much easier, option is a UDF which calls the VBA Evaluate function:

Public Function Eval(formulaText As String) As Variant
    Application.Volatile
    Eval = Evaluate(formulaText)
End Function

In cell A8, enter the formula =Eval(A7&"(A1:A5)"), and the result is 15.

 

[Defakto]

Woow, thanks. This is the first time I Heard about this function. That is really cool.

 

[Peter_SSs]

If using a UDF, and if you are only interested in those 5 functions, I don't think you need the intermediate step of constructing the function text on the worksheet.

Function sammc(sFn As String, r As Range) As Double
  sammc = Evaluate(sFn & "(" & r.Address & ")")
End Function

Formula in A8 is copied across.

Excel Workbook
	A	B	C	D	E
1	1
2	2
3	3
4	4
5	5
6
7	sum	average	min	max	count
8	15	3	1	5	5
Sheet1