SOLVED

Using Embedded functions

%3CLINGO-SUB%20id%3D%22lingo-sub-2639411%22%20slang%3D%22en-US%22%3EUsing%20Embedded%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639411%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20have%20in%20cell%20A1%20a%20function%20typed%20as%20a%20string%20such%20as%20Min.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20column%20C%20i%20have%20an%20array%20which%20for%20instance%20is%20(1%2C2%2C3%2C4%2C5%2C6%2C7)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20B1%20i%20would%20like%20to%20say%20%3DA1%26amp%3B%22(C%3AC)%20to%20find%20the%20minimum%20value%20but%20this%20can%20be%20variable%20so%20if%20i%20want%20to%20find%20max%20average%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20a%20way%20of%20doing%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2639411%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2639615%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Embedded%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1126724%22%20target%3D%22_blank%22%3E%40Stuart_Ballinger%3C%2FA%3E%26nbsp%3BIf%20the%20variable%20functions%20you%20intend%20to%20choose%20from%20are%20in%20the%20list%20of%20function%20numbers%20used%20by%20AGGREGATE%2C%20you%20could%20do%20something%20like%20in%20the%20attached%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20function%20to%20be%20used%20from%20the%20dropdown%20in%20A2.%20Then%2C%20B2%20will%20find%20the%20function%20number.%20I've%20used%20MATCH%2C%20but%20you%20also%20use%20VLOOKUP%2C%20XLOOKUP%20or%20INDEX%2FMATCH%2C%20if%20you%20intend%20to%20use%20a%20shorter%20list%20of%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20C1%2C%20the%20AGGREGATE%20function%20is%20executed%20using%20the%20function%20number%20from%20B2.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I want to have in cell A1 a function typed as a string such as Min.

 

In column C i have an array which for instance is (1,2,3,4,5,6,7)

 

In B1 i would like to say =A1&"(C:C) to find the minimum value but this can be variable so if i want to find max average etc. 

 

is there a way of doing this?

 

Thanks in advance

2 Replies
best response confirmed by Stuart_Ballinger (New Contributor)
Solution

@Stuart_Ballinger If the variable functions you intend to choose from are in the list of function numbers used by AGGREGATE, you could do something like in the attached workbook.

 

Select the function to be used from the dropdown in A2. Then, B2 will find the function number. I've used MATCH, but you also use VLOOKUP, XLOOKUP or INDEX/MATCH, if you intend to use a shorter list of functions.

 

In C1, the AGGREGATE function is executed using the function number from B2.

That works great. Thank you very much