Indirect Formula Function

[c8503]

Hey,

This is a quick "Does it exist?":

Is there an excel function that mimics the behavior of INDIRECT() but allows a text input to determine the function used?

Example: =ISTHEREAFUNCTION(A1,B1:B5)

When A1 contains the text string: "Average" and B1:B5 contains the cells to be averaged. Essentially the operation is itself variable.

This probably could be done with a VB custom function but I'm really hoping to avoid the typical slow-down that those seem to cause when I use them.

Many thanks
Chris

 

[JoeMo]

Would something like this be of interest?
E1:E10 contains some values. E11 uses cell G11 (text input) to calculate the function in G11 for the values in E1 to E11. A lookup table is needed as shown, but this can be placed out of sight or hidden for cosmetic purposes.

Excel Workbook
	E	F	G	H	I	J
1	1				Lkup Table
2	2				Average	1
3	3				COUNT	2
4	4				COUNTA	3
5	5				MAX	4
6	6				MIN	5
7	7				PRODUCT	6
8	8				STDEV	7
9	9				STDEVP	8
10	10				SUM	9
11	55		sum		VAR	10
12					VARP	11
Sheet1 (4)

 

[Jonmo1]

Excellent idea JoeMo

 

[JoeMo]

Excellent idea JoeMo

Thanks Jonmo1. Frankly, I was surprised to find that it works, at least for the simple test I performed.

 

[c8503]

thanks - that probably is the closest possible solution. can calc all options and then use the list of possibles as the basis fora drop-down. I was hoping for an elegant one but alas, microsofted!

 

[Peter_SSs]

thanks - that probably is the closest possible solution. can calc all options and then use the list of possibles as the basis fora drop-down. I was hoping for an elegant one but alas, microsofted!

I thought that was a pretty elegant solution! Not too sure what you were expecting?
If you just don't want to have a lookup table, even if it is hidden, you could build the lookup into the formula itself - see cell E12 below.

I'm also not sure about the fear of a user-defined-function as this is a pretty simple one. See cell E13 and the code below.

Function Fnc(f As String, r As Range)
  Fnc = Evaluate("=" & f & "(" & r.Address & ")")
End Function

And finally, another option, though I would not choose this one.
This would also require the file to be saved as a macro-enabled workbook though you don't need to do the vba as it uses an old Excel v4.0 macro function.
For the layout shown, the steps are

1. Define a Name as follows (Post back with Excel version if you need detailed instructions for this)
Name: myFunc
Refers to: =EVALUATE("="&$G$11&"(E1:E10)")

2. Use formula as shown in cell E14


In relation to speed, I timed the calculation of each individual cell in the range E11:E14 three times and the results are in A11:C14 with average calculation times for each function in D11:D14.
I didn't repeat the time tests for all other functions but did try a couple of others with similar relative results.
Given those results and the greater ease & flexibility of the UDF, I think I would go for that method, but you have some choices.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1					1				Lookup Table
2					2				AVERAGE	1
3					3				COUNT	2
4					4				COUNTA	3
5					5				MAX	4
6					6				MIN	5
7					7				PRODUCT	6
8					8				STDEV	7
9					9				STDEVP	8
10				Av Calc time	10				SUM	9
11	0.00105	0.00151	0.00189	0.0014833	5.5		AVERAGE		VAR	10
12	0.00104	0.00120	0.00106	0.0011000	5.5				VARP	11
13	0.00124	0.00116	0.00137	0.0012567	5.5
14	0.00121	0.00107	0.00115	0.0011433	5.5
15
Choose Function

#VALUE!

</td></tr></table></td></tr></table>

 

[Richard Schollar]

Thanks Jonmo1. Frankly, I was surprised to find that it works, at least for the simple test I performed.

I thought that was cool too! Would never have considered doing that myself.

 

[Jon von der Heyden]

thanks - that probably is the closest possible solution. can calc all options and then use the list of possibles as the basis fora drop-down. I was hoping for an elegant one but alas, microsofted!

I think it's very elegant; especially considering the alternatives. I'd be chuffed to bits had this been my problem.

 

[JoeMo]

Thank you Jonmo1, Peter_SSs, VoG, Richard Schollar, Jon von der Heyden and Robert Mika for your kind comments.

 

[barry houdini]

Excellent solution JoeMo

Note that if you are using Excel 2010 or later you can use AGGREGATE function in a similar way to SUBTOTAL and it will allow you to use some extra functions like median and mode......