Slashdot Mirror
Ask Slashdot: Spreadsheet With Decent Programming Language?
First time accepted submitter slartibartfastatp writes "Spreadsheets are very flexible tools for data analysis and transformations, the obvious options being MS Excel and LibreOffice. However, I found increasingly infuriating to deal with the VBA--dialect functions or (even worse) its translated versions. Is there any spreadsheet that allows usage of a decent programming language in its formulae? I found PySpread intriguing, but still very beta (judging from its latest release version 0.2.3). Perl or even javascript would be better options than =AVERAGE(). Do you know any viable alternatives?"
As old as the hills: http://siag.nu/siag/
Have you looked at Scheme in a Grid?
The last time I had to do something similar, and after I found the libreoffice calc to be just too weird for me, I just made a few tables in postgres, wrote a few functions and was done. YMMV, but it's definitely a lot easier to understand if you have any programming experience.
Better than a spreadsheet.
Scriptable.
Biggest downside: It uses a heavily enhanced VB Script.
Not a very elegant language, but way better than any spreadsheet that I know of.
Use C++ and write a xll or use C# and write a COM Automation plugin? Problem solved.
The latest version of MS Office allow writing apps in Javascript http://msdn.microsoft.com/en-us/library/fp160953.aspx
I am finishing my master's thesis and have used Gnumeric for all of my work, it is an effective spreadsheet although it is missing some features from Excel (to the frustration of my advisor).
The plugins are written in Python, and while I have not used them extensively they seem to be quite powerful compared to creating Excel macros.
Excel can already use VBA, which in turn can use IronPython.
Done.
There are plugins for Excel that let you use .net with it - so C#/VB.net or even C++/CLI.
www.r-project.org/
It's not exactly a spreadsheet, but Pandas is totally awesome and is useful for many tasks for which you might think of using a spreadsheet.
http://pandas.pydata.org/index.html
IPython Notebook is sort of like a combination of the normal ipython shell and an IDE. You interact via your browser but it connects to a normal python process on your local (or remote?) system.
http://ipython.org/ipython-doc/dev/interactive/htmlnotebook.html
I've used these tools together for many tasks for which I might otherwise have used a spreadsheet, particularly for "pivot tables" and time series analysis. Again, even combined they do not a spreadsheet make, but they are in many ways superior. They can handle very large data sets, and best of all you are doing it all in Python.
-73, de n1ywb
www.n1ywb.com
Instead of a spreadsheet with good programming just program and output a spreadsheet. CPAN has plenty of packages for this.
Google Docs (spreadsheets) support "Google Apps Script"... which is pretty much just javascript with a few extra classes relating to Google Apps -type objects (spreadsheets, gmail, contacts, etc). Pretty good support for most JS, and there is a decent set of (user-contributed) libraries that can be used. Also, since it runs on servers (as opposed to on a client machine), you can do some spiffy stuff with sucking in data from external sources.
Excel? You can expand Excel with - C#. Since, IIRC, V 2007.
Doing data analysis with Excel is like building an office building with nothing but a battery-powered drill from Home Depot.
This article at codeproject gives simple utitlies for building SS using .NET languages (including C#, F# IronPyton and others).
http://www.codeproject.com/Articles/21475/TblProc-OpenOffice-Calc-and-Excel
I think OpenOffice/LibreOffice can be interfaced with a number of programming languages
https://datanitro.com/index.html
I've used it a bit and it's pretty fantastic
Don't assume that a "low" version number means it's unusable. The project has commits going back to at least December 2009. Not all software is versioned with the assumption that 1.0 = finished.
I don't think this is quite what your after, and it is expensive as hell, but have you tried Matlab?
If you want a great spreadsheet: http://www.quantrix.com/
If you want to beef up the programming language but are fine with Excel: http://www.wolfram.com/products/applications/excel_link/
If you are talking non commercial: Siag (suggested above) is cool: http://siag.nu/index.shtml
This hasn't seen much activity in a decade but Haxcel: http://www.johanmalmstrom.se/haxcel/ is Haskell in a spreadsheet.
R is very powerful try this" http://www.omegahat.org/RGnumeric/
Can't you use Python on it, also?
Octave is free and is design to do matrix stuff, as most of what you will do in a spreadsheet is handling rows or columns of matrixes, it could be worth a shot.
What is the definition of a decent programming language?
It's a pretty ambiguous requirement.
C? Java? Python? Perl? Javascript?
Each is 'decent' in it's own way.
Another way to ask is this: What do you feel the shortcomings of the Excel VB language variant are?
Huh?
With version 4 a lot of the macro's can written in Python. You maybe able to expose those macro's as function and us it that way.
http://www.libreoffice.org/
This is a plugin I was looking a while back that may help as well:
http://oosheet.hacklab.com.br/
Do it with MySQL and a programming language of your choice and output to spreadsheet. Put your code in version control (git). Profit.
Or if you really need to be using Excel, try something like this: http://groovy.codehaus.org/COM+Scripting
Both LibreOffice and OpenOffice.org support macros in Python or Javascript. Chances are you already know one of those, so you don't even need to learn a new language.
Beta is broken and the link to classic doesn't work. Stop wasting our time or there won't be anybody left here.
Spreadsheets are actually terrible tools for data analysis. It's virtually impossible to document what you did with a spreadsheet, and make it reproducible and debuggable.
What you want is R, the Free software language based on Bell Labs "S" programming language for doing statistics and data analysis. R is like the fully outfitted machine shop compared to a spreadsheet's screwdriver and a hammer in a plastic box.
http://www.r-project.org
It's been ten years since I used it though.
Strange definition of what would be "decent" for working with a fucking SPREADSHEET. Never once have I looked at a large Excel file and thought "you know what would make this a whole lot easier? If it were written in fucking Perl and Javascript."
I'm guessing you're one of those "BOFH" types that likes to self-flagellate as penance for being a prick to the people you work with?
https://developers.google.com/google-apps/ --> https://developers.google.com/apps-script/ or directly to script.google.com (Full-disclosure: not affiliated - just a user.)
Spreadsheets are simple, for simple problems. If you're doing anything complicated, learn how to use a database. Any good database is very extensible.
Use this thing and you can code macros and user defined functions for excel in any language you want:
http://xlloop.sourceforge.net/
What are you doing with a spreadsheet that you find the built-in functionality so limiting?
It's possible that perhaps you're getting to the "hairy edge" of what a spreadsheet is capable of. Depending on your application, perhaps you need a more specialized -- or more general purpose -- tool, here.
Others have suggested MATLAB. If not that, how about Mathematica?
Maybe you've outgrown the scope of a spreadsheet and need a general purpose programming language, perhaps one that you can get a reporting package that suits your requirements. If you're using lots of VBA, why not go all out and use VB, or any other general purpose solution (C, Java, Python, etc., etc., etc.)?
Are you doing signal processing or control or other engineering stuff? Perhaps DaDiSP.
Some more info on your particular needs might get a more specific and useful answer from someone here that's done the same thing.
I am not a crackpot.
You can use any .Net supported language in excel, from IronPython to C# to Managed C++
You can use Perl to control Excel with OLE objects.
http://www.resolversystems.com/products/resolver-one/
I find that a decent SQL database platform is better for complex data manipulation. Install SQL Server 2012 Express Edition With Advanced Services (it's a mouthful, but it's free). It supports import/export from Excel spreadsheets, and a number of other data formats, and also includes Reporting Services for creating nice presentable reports without coupling the layout with your data storage (as with a spreadsheet).
Some SQL knowledge can take you a lot further than Excel will on its own.
it might not meet your needs because its spread sheet function came as an afterthought/alternative to an interactive geometric algebra tool. It has great power for some visualizations but not much for general data sets. IT is all open source and all in Java.
the link: http://www.geogebra.org/cms/
SLASHDOT: news for people who can't concentrate on work or have no life at all and got tired of yelling back at the TV.
How shall I make it hard for people to use Excel for just about anything.
Sometimes the solution to a problem is manual work with an Excel spreadsheet.
If builders built buildings the way programmers wrote programs, then the first woodpecker would destroy civilization.
Stop programming in your fucking spread sheet. It's not an application development system.
If you start having more code than you have data in there, you're doing it wrong.
On the other hand, I got paid a pretty penny to turn a spread sheet system into a real application not so long ago,
You don't have to use VBA to program Excel -- .NET has been an option for many years. You used to use Visual Studio Tools for Office (VSTO), but now the features of VSTO are baked into Visual Studio. Dev info here: http://msdn.microsoft.com/en-us/Office2010DeveloperTrainingCourse. And with Office 2013, you can adopt a HTML5/JS programming model. Get started here: http://msdn.microsoft.com/library/office/apps/jj220060(v=office.15). All info on Office development is rooted here: http://msdn.microsoft.com/en-us/office/aa905340.aspx.
Don't know if it would work for what you're wanting. Just throwing it out there.
Proverbs 21:19
But it's not quite a spreadsheet application.. I found it quite powerful last I tried it ~18 months ago, but I had trouble fitting the entire dataset in memory openrefine.org
http://www.projectdirigible.com/ - If you don't mind cloud storage.
Pretty much any Amiga oriented spreadsheet allows one to use AREXX
http://www.resolversystems.com/products/resolver-one -- spreadsheet python
All the power of Emacs Lisp at your fingertips!
It is free and it works better than Matlab.
Excuse me, but please get off my Pennisetum Clandestinum, eh!
Without knowing what you're actually doing it's kind of hard to make any recommendation.
Where I work, we find Tableau to be a good middle-ground between Excel and full SQL environments. It's not really a spreadsheet perse though.
Comment of the year
Consider learning an "executable mathematical notation" such as APL.
It has the advantage of looking like math formulas, naming entire matrices with a single letter and using symbols for the operations, while avoiding the pitfalls and chores of traditional programming languages, such as explicit loops.
APL was designed to allow non-programmers to express complex computations with ease, in a non-ambiguous, reproducible, executable way.
There are excellent commercial implementations (with trial or free-for-personal-use versions) such as AplX and Dyalog. They both have good tutorials. There is even a Try APL online site http://www.tryapl.org/
No spreadsheets are great because of shits like you who mock and laugh in people's faces when they only want to do some simple calculation tomorrow and you decide that it would be better for them to use a custom written library with awesome GUI interface. The problem with you is that they want to get done tomorrow not wait three months for you to get your shit in gear!
Seriously, I am a programmer, but I totally get why people use Excel. Programmers and devs have this problem in that they cant do something quickly because it needs to be abstracted, modularized, etc, etc...
"You can't make a race horse of a pig"
"No," said Samuel, "but you can make very fast pig"
What do you feel the shortcomings of the Excel VB language variant are?
My answer: I'm on Linux and those are my parameters.
Unfortunately, Libre Office and Open Office both give me giberish when I load the CSV files I use - unlike Gnumeric. Gunumeric doesn't have a programming language, though.
MS Excel is the gold standard.
For the rest of you people, yeah, yeah, yeah, yeah, I'm a "Troll" for saying something you don't like.
And they wonder why I post as an AC.
Of course 'it depends on the job', but I'd suggest having a look at SPSS, if you have access or are rich. Every college and university uses it.
en.wikipedia.org/wiki/SPSS
It has a code view for your queries (i am weird and only use SQL) and even some online 'libraries' of often used search strings (consult google). There used to be a FOSS alternative, PSPP, but I never used it.
I'd like to hear any feedback on my suggestion. The type of data analysis I did was either large data pulls based and de-duping on a few factors across 800K contact dbases in Oracle, Access, Excel, etc. or it was SPSS for doing geospatial, survey response, and anthropolgy factor analysis. We tested WiMax router signal penetration and correlated it with observational and respondent based computer usage data. SPSS handled the mix of data well.
Looking at other responses it appears that many are sort of dancing on the line between hard coding and dbase querying with their set ups...I'm not a coding wizard but I can at least get the concept. Never used most of the programs others have mentioned.
Thank you Dave Raggett
Try R, i think that it is a math programming language.
Be a programmer, not a Sheet enthusiast. I needed UI but simpler processing, so the ~LAMP stack worked for me (Linux, Cherokee, MariaDB, Python) . Python fits everywhere here from heavy data analysis to being web front-end. Often a one-line SQL statement did more than I needed.
Science & open-source build trust from peer review. Learn systems you can trust.
https://www.sharelatex.com/
I can't really call it a "decent" programming language, but R is all the rage these days. It's definitely good for working with tabular data. But it's nearly as bad as Perl in terms of there existing a quadrillion different ways to do one thing. Hell, there's even two different specifications for defining objects. Some of that isn't the fault of R so much as S, on which it was originally based.
If you pipe data in and out via SQL, you can live out all your tabular/2d matrix data fantasies without having to open a spreadsheet.
Spreadsheets are bad at just about everything. Use R instead. If you really need a spreadsheet, there are modules that act like a spreadsheet. But you'll be doing yourself a favor if you wean yourself off the spreadsheet teat.
R is better suited to this type of task than general purpose languages like Python. Most variables and functions in R are vectorised. It's very rare to ever have to write a for loop, which makes the language much more readable.
R is so good at this kind of thing that you don't need anything special to do a pivot table. Just use tapply() and sum(). There's also a 'reshape' package that is far more flexible than anything found in Excel.
Give me Classic Slashdot or give me death!
For symbolic math, Mathematica is vastly superior to Matlab. In my comprehensive exam, Matlab said if I increased the gain of a control system to 1E8, then the following error would be zero. However, for that particular control system, I knew that this result had to be wrong. For modestly large gains, the average of the absolute value of the error should have been a constant, and unaffected by the gain. At gains of 1E8, most physical systems go unstable. The issue shook my confidence on the written portion of my comprehensive exam.
SPICE and Mathematica computed the correct result. The key difference is Mathematica is a symbolic solver. It solves the formulas, without making unnecessary approximations. Spice is absolutely amazing for control system work. It analyzes stuff that most users would be unable to model with Matlab. In particular, SPICE models output to input capacitive coupling correctly, where most other models ignore the issue. Thus, SPICE will frequently predict that a system will be unstable if the gains are sufficiently large, whereas Matlab will often predict everything is good. Additionally, after knowingly blowing the results on the written, I verified the result on a physical system. I wanted to be really sure I had the correct answers for the oral portion of the comprehensive exam.
Matlab is a numeric computation package. In the case of control systems, it quietly converts Laplace transforms into discrete time z-Transforms before computing the system response. Never trust numeric results when they disagree with the theory. To this day, I still wonder if the professor that asked that particular exam question knew about this bug in Matlab, and deliberately asked the exam question from hell.
https://datanitro.com/
Using a spreadsheet and not a real program. There are countless frameworks and libraries for data analysis found in any real programming language if you accept the idea of writing a real program, opposed to trying to shoehorn a real language into a spreadsheet app. Remember, spreadsheets are designed for managers, and we all know how stupid managers are right?
Also the idea of looking for some obscure spreadsheet alternative just because you don't like the syntax is another fail. Not sure what you are doing, but there is a reason why Excel is a defacto standard in business circles and not "Bob's Awesome Spreadsheet XL".
I haven't thought of anything clever to put here, but then again most of you haven't either.
OpenSource: QTiplot http://soft.proindependent.com/qtiplot.html uses Python as its scriptiing language
Prop: OriginPro http://www.originlab.com/ - can use C , LabsTalk and has its own C-based X-functions.
Admittedly, they are geared towards scientific data analysis, but have powerful graphing and programming capabilities.
Entia non sunt multiplicanda praeter necessitatem.
You can install ActivePerl as a Microsoft ActiveScript engine and then program in Perl instead of VBScript or mix and match.
I have always thought a functional language like ML or Haskell would work well in a spreadsheet.
The compact functional code would visually fit well in a cell expression. And functional concepts like map and foldr would fit would work great for aggregating columns.
Also, it is insane that Excel does not have regular expression functions. Regular expression search/replace would work sooo well in a spreadsheet.
Check out Apps for Office. It allows you to use JavaScript in XL: http://msdn.microsoft.com/en-us/library/jj220082.aspx
That said, it's Office 2013 only & mileage may vary.
then use emacs LISP: (+ A1 A2)
There is an open source version of SPSS called PSPP. It is not nearly robust enough to replace SPSS in most cases where buying SPSS makes sense, but for small projects it works fine.
I browse on +1 so AC's need not respond, I won't see it.
+1 Informative, +1 Takedown
I'm kind of teaching myself programming (for web crap) but I have a weird database and data analysis background that is very helpful but causes me problems when I get deep into OOP shit...
Thank you Dave Raggett
A spreadsheet is something a user uses....
There is no God, and Dirac is his prophet.
A spread sheet is not a programming language. More than once, when my company has purchased some sorry back-water boondoggle, I have gone in and deleted all of their "Programs" which were nothing more than tricked-up spreadsheets. My favorite was a shared spreadsheet used for time and attendance that was nothing more than a front-end for a MySQL database running on someone's workstation. When I found the "developer" of that horror story I was actually allowed to walk him to the door.
Things that should never be seen in an excel spreadsheet but wind up there because of someone's attempt at "programming" include:
Credit Card numbers
Social Security numbers
Client-provided address lists
Bank account numbers and names
Just say no to using a spreadsheet as a program. If you don't know how to write a program you shouldn't learn by using a spreadsheet.
D3.js and table.js with google spreadsheet?
read section "Using Code Blocks in Org Tables" of the Org-Babel intro: http://orgmode.org/worg/org-contrib/babel/intro.html supported languages: http://orgmode.org/worg/org-contrib/babel/languages.html
I know the standard OS X AppleScript comes with bindings for ruby, perl and python as well as AppleScript, so in theory, you could use AppleScript to interact with Numbers (Apple's spreadsheet program), directly from the AppleScript bindings in Perl, Python or Ruby or whatever else Apple put bindings in (I think you can even do it in Cocoa/C).
Of course, I've nevery actually tried it, but it looks like it's possible...
What's wrong with the Average() function? It works fine for everything I've used it for.
This is the problem MavenWorks was created to solve:
http://www.mavenworks.com/Download.html
It was created so you could write C# code to do a lot of your calculations/data-access etc and do heavy duty calculations for e.g. risk analysis etc.
It's beta but I think has soon good ideas in it.
Emacs Lisp FTW!
I've always used awk for simple 'spreadsheets' and Python for more complex 'spreadsheets', since a spreadsheet is just a two dimensional array. If need be, you can always create the initial data set in a spreadsheet and view the final data set in a spreadsheet (with a little post processing if you need to use the spreadsheet's functions to visualize the data).
I like org-mode's spreadsheet. You can use Emacs' Calc package or plain Elisp for calculations.
http://orgmode.org/org.html#The-spreadsheet
Take a look at Panorama from www.provue.com.
Free demo for OSX & WIN
"No, Excel won because it was very much better"
..
Then why did Microsoft have to expend so much energy in killing Lotus 1-2-3
"Why was Lotus told that the shell would not be OLE enabled when In fact it is? Why was Lotus not given earlier warning if there was a change of plan? We're still lacking useful documentation on OLE in the shell - is there any"?
"OLE Forms are a counterpart to OLE controls and a cornerstone of the Cairo user interface architecture. We were recently informed by a Microsoft employee that responsibility for development of this operating system feature has been transfered to the Microsoft Office applications group."
"I'd be glad to help tilt lotus into into the death spiral"
Well over 20 years ago I used a spreadsheet called DFF/F that used Pascal to create the spreadsheet.
It was a goal-seeking spreadsheet when Lotus-123 and Visi-Calc ruled.
Does anybody remember it?
http://www.originlab.com/
It's Windows only, but it plays well with Wine. Makes beautiful plots. AND can be scripted for in both a BASIC-like language and what they call "Origin C."
Just use a Google Docs spreadsheet with their macro system, which is just javascript. Easy.
http://www.ais.com/Xess/xess5_product_sheet.html
Do not mock my vision of impractical footwear
Maybe give Resolver One a try?
http://www.resolversystems.com/
So what's wrong with average()?
It's a simple function call, the input is the data, the output is the average... if you were in C this would be a lot harder without some data formatting or in between functions.
Excel's programming language is definitely not bad. There are plenty of things to not like about Excel, but the language isn't one.
Try Matlab if you really need something more advanced, but don't count on loving the language if you hate Excel's...
Resolver One http://www.resolversystems.com/products/resolver-one/
It's a spreadsheet completely programmable in Python. Actually, the spreadsheet front end actually generates python code for each of the cells on the fly as you manipulate it. So you could look at it as a rather odd Python IDE.
No solution based on a spreadsheet is going to be clean or elegant. What we have here is someone who doesn't understand that VBA is a perfectly serviceable language for any task likely to be dreamt up in a spreadsheet, starting with (if we are talking MS Office), first class support for COM, which python sucks at, btw.
VBA is nothing more than a simplified, safe form of C with basic Object Oriented extensions. It has a very straightforward design that is fast, efficient and supports meaningful abstraction, encapsulation and polymorphism via interfaces. If you need something more complex (like advanced functionality found in C# or F#, for example) than dump the spreadsheet and get a real data model tier.
Similar concept, but the other end of the technological timeline is the ExtJs grid control (comes with some excellent docco)
You pick up a whole lot of complexity with the ExtJs framework, but you can pretty much implement a whole spreadsheet on it (someone has!), and it's all with Javascript since it's in the browser...
Might not be the same experience as local spreadsheets (no saving to a file :-( ), but it is extensible.
Better question: "Why are you trying to pound nails with a crescent wrench?"
...you can extend Excel using their SDK. See http://xll.codeplex.com for the easiest way to do this.
For all my scientific spreadsheeting purposes since 1995 or so, I have used Origin http://www.originlab.com/ . One problem is that it's only for MS Windows and doesn't run entirely correctly under Wine.
Regarding in-built programming support, it started with a in-built interpreter for their own c-style scripting language "LabTalk" (it is still present in the Origin), but they now also implement a c-compiler of some sort (which I don't use much).
I have found that it is very much a case of "the correct tool for the job". Origin is absolutely brilliant for processing large datasets and the graphing is fast and intuitive, but it is terrible for doing business-style spreadsheeting (such as storing student grades etc), for which I use LibreOffice or MS Excel.
"Those who don't believe in magic will never find it." - Roald Dahl
According to the feature comparison page LibreOffice has bindings for multiple languages including: LibreOffice Basic, JavaScript, BeanShell and Python.
Excel won because Microsoft played dirty with Lotus.
DOS version of Lotus 123 was far superior to MS Excel.
Microsoft used its inside knowledge of windows to kick Lotus out of business - very common strategy of Microsoft.
When Windows came Excel was working on them while Lotus had troubles for almost a year.
This + aggressive pricing killed Lotus 123.
The LibreOffice API allow many languages http://api.libreoffice.org/
You can build Excel Plugins using C++, XLL (excel loadable library) or XLAM (excel Add-In module)
Alternatively you can leverage Web apps (javascript) and SQL to create complex spreadsheets.
wow, noone has mentioned this yet. excel is great (as are all modern spreadsheets); the argument is the macro language. But what if there is an alternative to vba?
Analyrim has ability to do programs in each cell, many addons. However it was designed for terminals. The linux
version (curses) can be tried (www.gce.com). You start by compiling source...
It includes a relational (but non-sql) dbms. Since it was designed from scratch it is a bit different
from others. Versions for sunos, msdos, amigados, linux, vms, rsx11m, exist. There are many addressing modes for
cells.
You can get all the details here http://msdn.microsoft.com/library/office/apps/jj220082(v=office.15) but older version had support for C# if you used Visual Studio Tools for Office.
I'm surprised no one's mentioned Google Apps Script which is basically JavaScript and can script all the Google Docs apps plus other stuff, but even better for the Sheets app use Python with GSpread: http://burnash.github.com/gspread/
Alternatively you can leverage
Who the hell talks like this? This sounds straight out of a marketroid brochure. I mean, maybe you have a point, but how is that phrase not worse than "Or you can use" or something like that?
I always found python and scipy/numpy very efficient and flexible for matrix manipulations (and that's what most spreadsheet operations are, right?).
So unlike you have some numerical functions and re-invent a language around it, you start with the complete proper language and just add the numerical stuff.
Unless something has changed you can program Excel from any language that supports microsoft's windows scripting host system. I've written scripts to control Excel in both perl and python
http://en.wikipedia.org/wiki/Windows_Script_Host
You can use C++ in Microsoft Excel. You'll need an external compiler though.
First, VBA is BASIC with some additional features, extremely simplified, and well documented. You are likely trying to use python or something in unneeded situations or what you're doing is too complex for excel. Stop.
There are well documented wrappers for C, C++, and C# among many other languages for VBA. You should be able to accomplish a lot with that. You can build out custom formulas. Between the latter feature and C/C++ you should be fine. There are decent libraries for SAS which can model anything you can think of (as well as communicate with SAS graphs among other features - not a big SAS guy).
You can create nearly anything with a core VBA solution given the amount of open source solutions. R is sweet depending on what you are doing. More details would help decide if you are overreacting to VBA or truly need to go in a different direction
Spreadsheets were just a solution for people who couldn't program. If you want to have a grid of input fields and program around it, look for Lazarus. It's a rapid development environment primarily aimed at GUI applications. It's platform independent so you can compile (and I mean compile to native machine code!) to the usual platforms like Linux, MacOSX and even Windows. Apparently it even works for Android and Windows CE, but I haven't tried that yet.
With Excel you would use either the native Windows COM interface to Excel or on Linux, use one of the two Python libraries that read and write Excel spreadsheets. With Open Office and offshoots like LibreOffice you can integrate Python into the application in a similar way to VBA but still have all the power of Python and its massive collection of libraries at your disposal.
But there is even a better way. Get Resolver ONE for Windows and delete Excel. Use it just like Excel to build a worksheet through the GUI, and then when you open the scripting facility, SURPRISE!, While you have been building the spreadsheet, Resolver ONE has been turning it into Python code which you can now modify and extend. And, of course, debug, if your calculations aren't coming out right. And since the whole spreadsheet has turned into a Python app, you can also write Unit tests and prove that it is correct, and that it stays correct whenever it is modified or extended.
Excel is so old school.
You're right, the documentation for python or javascript macros in LibreOffice or OpenOffice.org are quite poor. Basically it's missing, and for such a sought after feature, this is quite a problem. Also missing are the docs for using java macros. I'm not sure what else LO and OOo can support, there might be more, but even these three are a major advantage that could use publicity.
Beta is broken and the link to classic doesn't work. Stop wasting our time or there won't be anybody left here.
It should be possible to program in C in Excel. I haven't tried but I've heard it can be done. Just look around. If it can be done, there's bound to be information about it.
A rather unorthodox approach for adventurous minds: ANKHOR FlowSheet
It is a kind of "LabVIEW" tailored to business problems and processing table based data. In a blog article these guys talk about how they tried to solve the main problems of spreadsheets (difficult inspection, inflexibility, limited reuse, limited scalability and difficult deployment) using visual dataflow programming.
Like when I want to actually look at my data in column format (scrolling, frozen panes, column hiding, conditional cell colouring anyone?). Or when I need to edit it (e.g. convert ascii strings to something numeric using search/replace). Or when I want to do a quick interactive pivot table. Or a quick sum or count. Or when I want to try out one or two formulas or expressions before I start coding them. O r when I just need a small table to look good for insertion into a document (the best Latex table editors that I know are plug-ins for Excel or Calc: format in spreadsheet, push button, copy-paste Latex code; works every time).
Of course it's possible to do most of those things in R too, if your time time has no value and if you love writing one-off code. I prefer to select the best tool for the job, and use that. Even if that sometimes means using VBA.
Interestingly I find myself using RExcel (integrating R and Excel) sometimes.
Most of the time however I have no time for zealots who tell me that I don't need X,Y, or Z because I supposedly can make do with A,B, or C too. They can e entertaining though, as long as you recognise them for what they are: rants from zealots.
Have you looked at Resolver One ( http://www.resolversystems.com/products/resolver-one/ ) - it lets you program in Python.
When you create a Google Spreadsheet you can create macro's (App Script) in javascript. You can even share or reuse other macro's from other people. It does have the AVERAGE() function as it mimics Microsoft Excel... but also adds lots of other functions specific to Google products.
Just wanted to say something positive about this comment. One great thing about Excel add-ins is that they get you a front row seat with business people (the ones writing the checks for your progamming skills). It is difficult to get them to provide decent specs for what they want, often they don't know exactly what they want, but if you give them an Excel add-in they will look at it and tell you "this is right", "that is wrong", "and, oh yeah, I forgot to tell you I wanted X". You can keep pushing the business logic into platform independent C++ that gives the correct results. When they need those numbers in a production system, you can give them the C++ library to link to. (People will pay more for that!) Calling C++ from just about any language is straightforward.
The most difficult problem to solve is usually just figuring out what the heck the business line needs/will pay for.
A great tool for creating old school xll's is http://xll.codeplex.com. Super easy to use and also generates documentation integrated with Excel.
I think Python and Javascript are neater than Perl for light non-programmer scripting.
You made some vague comment about not liking VBA, but really what's wrong with it? Excel has great support for interoperability with the rest of the MS stack, and most 3rd party software can export to Excel format. VBA is also quite powerful. It sounds like perhaps you're saying you don't know VBA and want someone to create an Excel with perl instead of VBA so you don't have to learn VBA.
Perl or even javascript would be better options than =AVERAGE(). Do you know any viable alternatives?
Not precisely what you are after, but Perl on Windows supports OLE/etc and allows you to connect and control the Office applications. (I played around only with the Outlook.) One can add a macro button to the Excel UI to call a script on the document.
Simply google for "perl win32 excel".
All hope abandon ye who enter here.
The way I tried to solve this is to make a tool to program objects with spreadsheet styled rules in java that just execute themselves when they need to.
http://deduced.org/
Look at it this way; Excel is by a zillion miles the most successful object oriented programming language ever.
Star Trek transporters are just 3d printers.
I believe they wanted to use spreadsheet as
https://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller
Casteism
You can have excel load dll functions. I wrote financial add-ins using this technique with all the real code in c++ with c wrapper exports. The vba was just a bit of glue code.
-- soldack
I have and I know many of my co-workers and friends have, over the years, gone from working with the data sets via a programming language and a RDBMS and exported the result sets into a spreadsheet format. I don't know if this applies to your question but think about it, if your problems are the lack of programability in a spread sheet then why not take it from the other end and go straight to programming and export your final set into a spreadsheet. You can create an excel spreadsheet from just about any common language these days whether it be VB, C, Perl, Python, Java, PHP, etc. I'm pretty sure you can import from a Excel spreadsheet from all of those same languages as well if that's where you need to import your original data from.