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.

 

[Joe4]

Welcome to the Board!

I would possibly look to use VBA to build the formula "on-the-fly", based on the value in cell A1. But, that may depend on where these formulas are going, what ranges are being used, and whether or not those two things are static or not.

 

[fhobbs]

So there is no easy way to do it.

 

[Joe4]

Well, it all depends on what your data looks like, and what the big picture is here (and what you consider "easy").
There may be another way to do what you are after, if we have a full understanding of what you are doing and why.

 

[Jonmo1]

I agree with Joe, it depends on your definition of 'Easy'.

Also which version of Excel are you using?
There's a fairly easy method with XL2010+

=AGGREGATE(MATCH(A1,{"Average";"Count";"CountA";"Max";"Min";"Product";"Stdev.s";"Stdev.p";"Sum"},0),4,A2:A3)

 

[fhobbs]

I have two long columns of data (A, B). In a third column (C) I want to have a formula that compares (min or max or sum etc) the data in each row of the data in A and B. But I want to be change the formula in the cell of (C) from a cell reference (lets say in D1). By changing the value D1 from min to max the formula in (C1) would change from =min(A1:B1) to =max(A1:B1).
Hope this helps.
Thanks much!

 

[Joe4]

How about this:

Why not have three hidden columns, one that does the MIN, one that does the MAX, and one that does the SUM, i.e.
E1: =MIN(A1:B1)
F1: =MAX(A1:B1)
G1: =SUM(A1:B1)

Then, in cell C1, enter this formula:

=IF($D$1="MIN",E1,IF($D$1="MAX",F1,IF($D$1="SUM",G1,"")))

So, then depending on whatever you enter into cell D1, the value you want will be returned in cell C1.

 

[Joe4]

OK, I have stumbled across a way that uses a little VBA to allow you to do what you want (build your formula dynamically).
Here is the link here: http://www.mrexcel.com/forum/excel-questions/62067-eval-function-without-morefunc-add.html

So, if you just copy and paste this to VBA:

[COLOR=#333333]Function Eval(Ref As String)[/COLOR]
[COLOR=#333333]    Application.Volatile[/COLOR]
[COLOR=#333333]    Eval = Evaluate(Ref)[/COLOR]
[COLOR=#333333]End Function[/COLOR]

Then you can use this in your Excel formulas.
So, if D1 had MAX, MIN, or SUM in it, you could use this formula to do what you want:

=EVAL(D1 & "(A1:B1)")

Of course, VBA will need to be enabled for this to work.

 

[fhobbs]

Great! I will give VBA method a try soon. I see how the hidden columns would work also but not nearly as clean. Thanks

 

[Joe4]

Great! I will give <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> method a try soon. I see how the hidden columns would work also but not nearly as clean. Thanks

True, as long as you are able to use VBA (and whoever is using the workbook makes sure to enable VBA).