Function as cell reference?

[fhobbs]

Can you write a formula where the function you want to use is a variable (cell ref)?
I’d like to be able to change the function in a formula from a cell reference. Ex.
=max (A2:A3), where the function “max” is defined from a cell reference in cell A1, so I can change the function to min, sum, etc by changing cell A1.

 

[fhobbs]

The VBA codes work fine. Thank. But in the formula =EVAL(D1 & "(A1:B1)") the "(A1:B1)" is not seen as a cell ref so it does not increment when copied down so it only works for the first of many row. I tried =EVAL($D$1&"("&A1:B1&")") which makes A1:B1 a cell ref but not a good equation. Ideas?

 

[Joe4]

That is because it is a string in our formula (we are using string to build the formula, and then use EVAL to evaluate it).
We can use the ROW() function which dynamically returns the row number of the cell that it currently resides in, i.e.

=EVAL($D$1 & "(A" & ROW() & ":B" & ROW() & ")")

and then that will return the desired result when you copy it down the row.

 

[fhobbs]

Perfect! Thanks much. So much to learn about excel.

 

[Chrisdontm]

Excuse me if I am missing something, but it looks like what you where wanting was this formula;

=CHOOSE(IF($D$1="MIN",1,IF($D$1="MAX",2,IF($D$1="SUM",3))),MIN($A$2:$A$3),MAX($A$2:$A$3),SUM($A$2:$A$3))

Of course setup D1 with a drop down menu of a list of the three functions you mentioned.

If I missed something, please forgive my interruption...