Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 5 down vote favorite

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?

share|improve this question
Digging through several Google results, I'm pretty confident that (as of the '09 version) Numbers does not provide any way to create custom functions. – iconoclast Aug 31 '12 at 15:13

5 Answers

up vote 6 down vote

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.

share|improve this answer
Hehe, same time as you. Looks like you're link is for Excel 2007, while mine is Excel 2003. Good to have options. – dangowans Aug 30 '12 at 17:58
6  
Libre Office will allow custom functions as well help.libreoffice.org/Calc/User-Defined_Functions – EBGreen Aug 30 '12 at 17:58
1  
Very interesting. This could possibly make my Excel life much happier, but I assume it has all the problems that go along with macros: if you share your spreadsheet with others they get a Big Scary Warning(TM) that opening the file could cause tooth decay and disembowel their pets, as well as enlist their computer in a botnet aimed at taking down the department of defense. – iconoclast Aug 30 '12 at 19:08
1  
Just let people know that the file contains macros, that way any warning wont send them into panic. – Keltari Aug 30 '12 at 19:23
1  
I don't really consider it a separate answer. To be honest with you the answer to the question as presented is simply yes. I could easily see people voting to close the question since it shows very little effort at prior research. It also invites multiple perfectly valid answers with potential discussion of the pros and cons of the various options. Having said that, feel free to make it an answer if you would like to. – EBGreen Aug 31 '12 at 19:18
show 2 more comments
up vote 4 down vote accepted

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

share|improve this answer
I don't think I'll get any points for it if I accept my own answer, but no other answer was as comprehensive. (Which is my fault, actually, because of the way I worded the question.) I've up-voted all the useful answers and comments that helped me put this together. – iconoclast Aug 31 '12 at 19:31
2  
You may add the classic spreadsheet, Lotus 1-2-3, which has supported user-defined @functions since before most of the other competitors even offered spreadsheets. – Ben Voigt Aug 31 '12 at 19:43
That's interesting, since one of the notable flaws in Lotus Notes' formula language is that it does not support user-defined functions. I've always assumed that it was initially borrowed from 1-2-3, as many of the functions have the appearance of being very much like those you'd find in a spreadsheet. – iconoclast Aug 31 '12 at 20:05
up vote 2 down vote

I found a Microsoft article discussing how to create custom function is Excel 2003.

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

I imagine this applies to newer Excel versions as well.

share|improve this answer
up vote 2 down vote

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:

  1. 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.
  2. 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.
  3. In VBA, right-click Modules and choose Insert>Modules.

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

  5. Save your Module and return to Excel.

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

share|improve this answer
1  
Have you published your functions anywhere? Would you please? I'd love to see what you've come up with. Thanks! – iconoclast Aug 31 '12 at 18:40
up vote 1 down vote

In 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.