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?
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.
|show 2 more comments|
Apple iWork Numbers
This cannot be done in Numbers, unfortunately.
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.
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:
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.
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