Does any spreadsheet allow you to create your own functions?
I've always been frustrated when using spreadsheets by the inability to define my own functions when there is no existing function to (cleanly) do what I want. Does any spreadsheet offer this ability?
06 Answers
I dont use spreadsheets other than Excel, but Excel is capable of doing just about anything using VBA, including custom functions. Check out this article to see a basic example.
7Apple iWork Numbers
This cannot be done in Numbers, unfortunately.
Google Apps
Custom functions are supported in Google's spreadsheet, using JavaScript.
LibreOffice Calc
LibreOffice Calc supports custom functions.
Microsoft Excel
Custom functions are possible in Excel, going back at least as far as Excel 2003.
Given that there are several choices, it's hard to know which one to choose.
If you (or your employer) have a prior commitment to a Microsoft-based solution, or (conversely) to an open-source solution, then the decision is basically already made. If you're an Apple fan (as I am) you'll use Numbers whenever possible, and only abandon it when you need features beyond what it provides. But (in my opinion) the solution with the biggest upsides and fewest downsides is Google spreadsheets:
You don't have to worry about (real and perceived) threats from macros in your spreadsheets. You don't have to worry about whether you can share the file with others: you just grant access to anyone you want, and it's "in the cloud" for them whenever they want. Soon you'll even be able to edit your spreadsheets offline.
You're exposing your information to the Google Information Borg (if you even care), and you're giving up some elegance (as compared to Numbers) and a fuller feature set (as compared, at least, to Excel, if not also to the others), but those may not be a big deal.
If, on the other hand, you're already using Excel (as most of us are) and you can live with the downsides of both the risks involved in using macros, and of the annoying warnings to anyone you share your files with, and of the messiness of managing your functions (if you put them in a separate file then you'll need to distribute that to others every time you give them a spreadsheet using those functions, but if you put them inside the file then you'll be doing a whole lot of copying and pasting, and your later improvements won't propagate to earlier spreadsheets) then a very strong case can be made for sticking with Excel. With very little doubt it's the most powerful of the bunch, and it really doesn't have a bad UI by any means.
3This is rather simple using Funfun in Excel. Funfun allows you to use JavaScript directly in Excel so that you could define all sorts of functions by yourself. With the help of the Funfun Excel add-in, you could use the data in the spreadsheet and also output data into specific cells.
Basically what you need to do is
1). Insert the Funfun add-in from Office Add-ins store
2). Create a new Funfun or load a sample from Funfun online editor
3). Write JavaScrip code or define your own function.
4). Run the code directly in Funfun
I defined a sample function in Funfun online editor that could calculate the probability density using the mean and standard deviation stored in the spreadsheet. You could check this sample on the link below.
You could easily link this sample to your Excel using the Step2) above.
Disclosure: I'm a developer of Funfun.
I found a Microsoft article discussing how to create custom function is Excel 2003.
I imagine this applies to newer Excel versions as well.
Excel does this great, using VBA. I have a number of custom functions that I've created, from basic to complex, to do everything from returning the current cell's sheet name to custom formatting charts. Many of my functions began in Excel 2003 and I've migrated up to Excel 2010(64bit). It's worth noting that it takes a bit of extra programrming to get them to work in both 32 and 64 bit environments. To create your first custom function:
- I recommend using a clean, separate workbook that you can save as an .XLA file that can be used as an Add-In. This allows you to call your custom functions from any of your other workbooks, instead of limiting them to the workbook you use them in.
- Select Visual Basic from the developers tab. If that tab isn't shown, you'll need to activate it in Options>Customize Ribbon>Main Tabs.
- In VBA, right-click Modules and choose Insert>Modules.
Enter your code (the following will return the sheet name for the cell you call it in):
Function SheetName(rAnyCell)
Application.Volatile
SheetName = rAnyCell.Parent.Name
End Function
Save your Module and return to Excel.
In any cell, type =sheetname(A1) and it will return that worksheet's name.
You're correct about this carrying Excel's Macro "baggage", so you'll need to keep that in mind regarding the environments in which it will be used. But, on the whole, its a huge benefit and allows substantially improved workability.
1In the Google Apps environment, you have the possibility to create stand-a-lone functions with Google Apps Script (GAS). This is cloud based javascript quite similar to Google Webtool Kit (GWT).
Within a google spreadsheet you can build functions that are available only within the workbook. See the following answer on Super Users; this example is used as a function.
Working example included: custom functions
