Indirect Formula Function

c8503

New Member
Joined
Mar 3, 2010
Messages
28
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
EFGHIJ
11Lkup Table
22Average1
33COUNT2
44COUNTA3
55MAX4
66MIN5
77PRODUCT6
88STDEV7
99STDEVP8
1010SUM9
1155sumVAR10
12VARP11
Sheet1 (4)
 

c8503

New Member
Joined
Mar 3, 2010
Messages
28

ADVERTISEMENT

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! :rolleyes:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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! :rolleyes:
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.
Code:
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
ABCDEFGHIJ
11Lookup Table
22AVERAGE1
33COUNT2
44COUNTA3
55MAX4
66MIN5
77PRODUCT6
88STDEV7
99STDEVP8
10Av Calc time10SUM9
110.001050.001510.001890.00148335.5AVERAGEVAR10
120.001040.001200.001060.00110005.5VARP11
130.001240.001160.001370.00125675.5
140.001210.001070.001150.00114335.5
15
Choose Function
#VALUE!
</td></tr></table></td></tr></table>
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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. (y)
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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! :rolleyes:
I think it's very elegant; especially considering the alternatives. I'd be chuffed to bits had this been my problem.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thank you Jonmo1, Peter_SSs, VoG, Richard Schollar, Jon von der Heyden and Robert Mika for your kind comments.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Excellent solution JoeMo (y)

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......
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top