Define user-defined functions from the Name Manager
Sometimes, a user-defined function is not so complicated that it requires VBA and thus requires us to ditch the XLSX format. We should be able to define our own functions in the Name Manager.
For example:
EXACTMATCHRANGE(a,b,c,d) = INDEX(d,MATCH(a,c,0)):INDEX(d,MATCH(b,c,0))
NEWVLOOKUP(a,b,c,d) = OFFSET(INDEX(b,MATCH(a,b,d)),0,c)
YESTERDAY() = TODAY()-1
XNOR(a,b,c,...) = NOT(XOR(a,b,c,...))
FIRSTMONDAY(y,m) = DATE(y,m,7)-WEEKDAY(DATE(y,m,7),3)
SHEETNAME(ref) = =MID(CELL("filename",ref),FIND("]",CELL("filename",ref))+1,255)
LASTWORD(s) = RIGHT(s,LEN(s)-FIND(" ",s))
REFADDRESS(a,b,c,d) = ADDRESS(ROW(a),COLUMN(a),b,c,d)
SHORTCIRCUITAND(a,b,c,...) = IF(a,IF(b,IF(c,...)))
RMS(a,b,c,...) = SQRT(SUMSQ(a,b,c,...))
RANDBETWEENFLOAT(a,b) = RANDBETWEEN(a,b-1)+RAND()
You get the idea.
12 comments
-
Kenneth Barber commented
More specifically, this is in beta. The LAMBDA function lets you define your own function, and you can give it a name in the Name Manager.
It would be nice if the LAMBDA function supported variable-length argument lists (e.g. like my XNOR and RMS examples above) and optional arguments, but that can be saved for another suggestion, possibly for another function. The LAMBDA function as it currently is still adds a lot of expressive power to Excel's formula language.
-
Corey Becker
commented
This is done!
-
Ewald
commented
That would be a great alternative to UDFs.
That kind of (safe) macros should run without user confirmation in .xlsx (non-VBA-enabled) files. -
AHarris
commented
For functions that don't require an input parameter like YESTERDAY in your examples, you can already do this with the name manager. All that is needed (likely it's major work) is accepting input parameters, to a named formula, that can be used in the formula.
-
Will Ayd
commented
This is a great idea that could make workbooks a LOT more readable. Citing a specific example, I had a client that needed items to summed across fragmented columns in a table. I ended up having to write something like:
=INDEX(Table[Col1], <row_num>)+INDEX(Table[Col2], <row_num>)+INDEX(Table[Col3], <row_num>)
In theory a "partial function" F could be defined with this feature to make this:
=F(Table[Col1])+F(Table[Col2])+F(Table[Col3])
Which becomes much more readable and mitigates the risk of typos or silly mistakes in having to repeatedly provide the same function arguments in the aforementioned call
-
Nick
commented
A comment added to my previous comment: You can actually achieve something like what I wrote using the FORMULATEXT formula with the hidden EVALUATE formula that is only available through the Name Manager.
It could still be a lot easier if all of these could be UDFs though.
-
To prevent potential conflicts with the names of future functions, perhaps all UDFs can have the prefix "UDF.". For example, UDF.YESTERDAY().
-
Uncle Bob
commented
Great idea.
Excels Define names already implements "mini UDFs". It should not be hard to extend it.
For example here is a working "mini UDF"
Step 1 Enter the following into a new workbook
A B C D
1 ca California
2 ky Kentucky
3 ny New York
10 ky =miniUDFstep 2 Highlight A1:b3 and name it 'xluState'
Step 3 select B1 then alt M M D and define
. name: miniUDF
. Refers to: =VLOOKUP(Sheet1!a1,xluState,2,0)And just like magic cell d10 gets "Kentucky"
So, all microsoft need is to modify things so that this works
. name: miniUdf(var1)
. refers to: = vlookup(var1,xluState,2,0)Ideally var1 would be a variant that would usually be a range, but could be anything that is consistent with the UDF.
-
-
Nick
commented
To clarify my comment 'make functions first class objects' you could have a cell function as follows:
= FormulaAt(reference, [arg1]. [arg2], [arg3]...)
usage:
Cell A2: = A3 + A4
Cell B2: = 3 * FormulaAt($A$2, B3, B4)What made me think of this is that one time I wanted the spreadsheet user to enter a formula into a cell, and I then wanted that formula to be applied to a collection of other data in a protected sheet (without using VBA).
That's fairly niche though, so the suggestion of doing this in the Name Manager or a Function Manager is probably cleaner. Documentation could also be specified.
-
Nick
commented
I agree that it would be great to be able to have simple UDFs without going to xlsm format (which I never want to send to clients if I can avoid it).
Generally speaking could describe this request as 'make functions first class objects' right? Whether a good place to define these is a new function manager, or in the name manager, or even in spreadsheet cells which other cells can point to, I'm not sure.
This would also clear up quite a few of the requests for new formulae on this uservoice site e.g. "Two new IFs", because people could just define their own.
-
If Microsoft collects data on commonly used user-defined functions, then they can determine some of the functions that they should add as built-in ones without having to ask people what they would like.