Spreadsheets for Scientific Computing?

redcliffe asks: "Sometimes it's much easier to create a spreadsheet to do some mathematical calculations for physics than to create a whole new program to do the calculations. It's also handy to be able to easily change one figure and have all your other calculations update. But there a certain types of calculations that normal spreadsheet apps like KSpread don't seem to handle well. Anyone know of a spreadsheet or something similiar designed for scientific computing? I've seen GNU Octave, but that's almost like writing a whole program, and without a GUI it's hard to learn quickly."

Comments

Something else

[Goldsmith]

Spreadsheets are usefull, yes, but if you're trying to do something your spreadsheet can't handle, chances are you should probably not be using it.

I suggest you use a program like Mathmatica, or program in Fortran or similar "easy" programming language. Spreadsheets are really only usefull in physics for data collection and mining on a small scale.

Re:Something else

[Louis_Wu]

Occasionally a "spreadsheet problem" has one or two bits which are really "math-program problems". Your straight-forward calculation can sometimes have a quadratic in the middle, and sometimes it's a quintic, while other times it is a quartic - and then you have to integrate the bloody thing. If the equation was constant, you could just do the integration on paper and plug the equation into your spreadsheet and get-on-with-it. But you can't, you have to somehow make Excel (or Gnumeric) integrate your polynomial. And I don't think that they can.

Enter Mathematica.

Mathematica has an add-on available which lets you link to Excel, and link from Excel to Mathematica. You can send that equation over to Mathematica, have it evaluate it, and send it back.

I've never used this feature, but I hear that it's a real life-saver. Plus, Mathematica is one of the easiest math programs to start using, and one of the most powerful if you keep using it.

Re:Something else

[hubie]

Just curious, but what do you consider a "hard" programming language, or at least one that would be considered "harder" than FORTRAN (from my point I certainly wouldn't say "C" because it is pretty much the same as FORTRAN---they just have different ways of looking at things)?

Re:Something else

[Goldsmith]

C is exactly what I was thinking of.

I think it's just that people have different impressions of the languages. Now, for all my talk, I can't actually program in C... so would I recommend it? Because I have been denied a (very nice) lab position simply because I have lots of FORTRAN and IDL experiance, but no C experiance. And it is highly recommended by my current employer that I learn C as soon as I can.

In a way... "hard programming language" isn't really my phrase, it's the word of the physicist who didn't hire me.

Re:Spreadsheets

[Associate]

'not powerful enough' is probably a poor choice of words. How about 'handles inefficiently'?

Re:Spreadsheets

[redcliffe]

Well that's what I'm trying to find, a spreadsheet style app that handles this sort of stuff well. Like if I'm changing a few values, it very nice to be able to see instantly what the result is. Writing a whole program to do this would be hard. Thanks,

David

What don't they handle well?

[OldMiner]

This isn't going to be of any help, but I have a few questions.

I'm sure there are things which spreadsheets can't handle well. Off the type of my head I can think of derivatives, integration, and solving simultaneous equations. But I'm wondering what in particular you've found spreadsheets unable to do.

Further, you didn't mention a platform on which you are operating. I assume KDE running on Linux, since you mentioned KSolve. Are your restricting your search to this platform?

And, dare I ask, have you tried Excel to see if it provides functionality to do this built in, via macros, or via VB? Although VBA may not be great fun, it may allow you to do what you want without doing quite so far as writing a full program.

Re:What don't they handle well?

[redcliffe]

Well the things you mention, and it is also difficult to create and use complex mathematical formulas.

Well Linux is my operating system of choice, but I don't have huge objections to windows. Excel is no better for what I'm doing than KSpread though so I use Kspread. VBA is good, but I'm trying to avoid having to code to get this to work. Thanks

Re:What don't they handle well?

[dutky]

OldMiner wrote

I'm sure there are things which spreadsheets can't handle well. Off the type of my head I can think of derivatives, integration, and solving simultaneous equations.

Huh? None of the things you mentioned are handled badly by a spreadsheet, at least not in the numerical sense. Integration is simply a sum of cell data. Derivation is obtained by taking the difference of adjacent cells. While solution of linear systems is a bit trickier, many spreadsheet packages either come with solver engines or have scripting langauges in which a simple solver can be written.

The one thing that I have found lacking in most spreadsheets is an easy way to fit curves to data sets. This is pretty glaring omission, given that you have both the data sets and a graphing tool in most spreadsheets. But then, I suppose that curve fitting is an alien concept to most of the financial world (though, I would think, statisticians might have some use for it).

A quick browse, however, through the Gnumeric Manual indicates that it has a linear solver and a host of statistical tools, which should allow you build almost any other numerical analysis tool you want.

Now, if you want to get symbolic results from a spreadsheet, you are probably screwed. But there are other tools that are better suited to such tasks than spreadsheets.

Scientific Spreadsheets

[Dymaxion]

You want a program like Origin -- a spreadsheet designed for scientific computing. While it's both very much not free and windows only, sometimes you just have to use the right tools for the job. A free or even just Un*x friendly replacement for Origin at its level of sophistication would be a very Good Thing. I'm hoping that there are other, similar software packages out there, but that's the one I'm most familiar with.

Re:Scientific Spreadsheets

[fxj]

There is Scigraphica (scigraphica.sourceforge.net)
which might be what you want. It features spreadsheets, graphics and python as programming language. Another good spreadsheet is VisAD (www.ssec.wisc.edu/~billh/visad.html) which is written in java and jython and thus works on Linux AND M$-windoze.

There are several to choose from

[RJHill]

The Scientific Applications on Linux site has a few entries for spreadsheets and many more for data processing and visualization.

UK NPL view on scientific software

[Yarn]

The NPL (UK version of NIST) tested a variety of software used for scientific purposes, some of the results are in Metromania 13.

Matlab

[clark625]

A previous poster correctly stated that if you can't do it easily in a spreadsheet, you're probably needing another tool altogether. I would suggest using Matlab. Mathematica is nice, and so is Maple. And fortran's great if you want to push around bits, although C is used more by the engineering community.

Matlab's greatest strength is that it works magic with matricies. By using a spreadsheet, you aren't terribly far off from Matlab's strength, as I'm guessing that you've got several arrays. The matlab engine is also very robust and has nearly as many options available as fortran or C would. Can't beat that.

Re:Matlab

[Col. Klink (retired)]

Octave is a Free version of Matlab.

Even Octave/Matlab are programming languages, they aren't that hard to learn and are extremely powerful. In fact, I've seen people make spreadsheets more complicated than matlab programs.

Re:Matlab

[alyosha1]

Yes it does have a GUI. I had no previous experience with it until a few weeks ago, when I needed to visualize some large data sets that were making open office swap horrendously, and found it very easy to import the data and get some nice 3d plots. (I'm using version 6.1 on Windows). It handles much bigger data sets than a spreadsheet will, which is important in scientific applications, and is generally much better suited to this kind of problem than a spreadsheet

Re:Matlab

[CompVisGuy]

Spreadsheets aren't designed to be used in Scientific Computing (they are more suited to financial applications) -- there are other tools for the job that are explicitly designed for that purpose.

I use Matlab, and although it has some niggling features, it is a good product. It is, however, expensive. GNU Octave is similar to Matlab, but it isn't as feature rich or as well tested. I haven't used Mathematica or Maple, but I hear these are also fine pieces of software.

In terms of a GUI, Matlab does have a GUI, but most of the interaction with the system is by typing commands (such as "mean(x)" which computes the mean of the values in x), or by writing files containing sequences of such commands (i.e. programming). It is very easy to learn the basics.

In scientific computing, GUIs aren't really "where its at" -- get over it, you'll be pleased you made the effort. That said, Matlab has a very easy to use feature that allows you to put together GUIs, so that you can make user interaction with your program easier (e.g. if you wanted to get an expert radiologist annotate a set of medical images and store and process the results using your program, this could be put together very quickly and easily -- there are, obviously, many other applications!).

Try and get some demos of Matlab, Mathematica and Maple, devote a day to each to do some tutorials, and see which one you like best.

MathCad

[sysadmn]

Consider MathCad. Back when I was doing control theory classes, the interface seemed much more intuitive. You 'wrote' a page of equations, plots, etc. and they were solved automatically. You could even write live reports. It was great for lab and homework writeups. The screenshots don't do it justice. Sadly it is not free, and is windows only.

Re:Spreadsheets

[mwr]

I'm probably missing something, and I'm definitely biased against spreadsheets in general for most scientific work (MSME here, working with grad students daily on this sort of thing).

But given that most engineering/scientific spreadsheets have a format of:

• Input parameters up top
• Lots of formula cells doing calculations below, many of them hidden when the variable isn't important
• Results cells at the bottom

I'm just not seeing the big advantage to it versus:

• Window running a shell for Python code, (or a Matlab window)
• Window with $EDITOR editing Python or Matlab code

In the spreadsheet version, your variable names tend to be cell locations. In a program, you pick meaningful names.

The complexity of writing formulas isn't terribly different, assuming you're using a language with a reasonable math/stats library (Matlab definitely qualifies, and Python should, too).

Now a spreadsheet does have mind-numbingly simple graphing, as long as you don't get too complicated. But at least in Matlab, if you have a pair of row or column matrices x and y, it's plot(x,y).

Instant gratification in a spreadsheet isn't terribly more instant than (up-arrow) (Enter) in the shell or Matlab window mentioned above.

And neither Python nor Matlab compare in learning curve to Fortran or most other languages. You could likely be competent in either of them inside of two days.

See here for one example on projectile motion. The functions aren't required to be split out, but are good practice.

You could try IDL

[HuskyDog]

Another possibility would be IDL. I believe that it is very similar in concept to Matlab (although I have never used the latter). It certainly works just fine on x86 Linux and there is even a version for Alpha Linux (although that is about to be discontinued), but it isn't cheap.

Most of my colleagues are Windows addicts who battle away getting Excel to handle huge data sets. I do almost all of my work in IDL and find that it works just great.

Re:Excel

[foobar104]

Cool UID, SpatchMonkey.

Rather than just leaving you moderated at -1, I thought I'd speak up and tell you why you're off topic.

The poster wanted to hear about programs for doing scientific calculations. You suggested that he use Excel to build a database. That's pretty far from what he was asking about.

I just hate it when people moderate my comments down and it's not entirely clear why. So I figured I'd speak up, why not.

related to octave

[Pauly]

Octave is primarily used as a plotting tool, no? If for you the answer is yes, than by all means check out Scigraphica. Scigraphica reminds me a great deal of the wonderful plotting packages like Stanford Graphics that I once used as a budding scientist. It claims to be aiming for the functionality of Microcal Origin. I can't speak for its spreadsheet capabilities, but its plotting capabilities have impressed me more than any other GPL equivalent.

Re:Excel

[SpatchMonkey]

Thanks for your explanation; I was thinking in terms of storing logged lab data.

(My post was a troll/joke anyway -- I've worked in too many places that actually do use Excel to record important data in this manner.)

TK Solver

[Azriel_S]

Try TK Solver. It is designed for solving simultaneous equations. I used it in college to solve some nasty problems dealing with internal combustion engine cycles. Back then it was on UNIX, but it looks like it is only Windows now.

Re:Spreadsheets

[Bruce Hollebone]

A major limitation is that Excel can only handle small data sets. A worksheet can only have 32000 rows (or is it 32k? I forget) and even fewer columns. That may sound like a lot, but it's not really.

Also, Excel's graphing package is awful (poor layout control, clunky interface).

KSpread is open source you know

[bluGill]

Open source can always use programers who have an itch to scratch. Without knowing your problem I can't really say more, but it would be a good selling point for KSpread to be the only spreadsheet that can do what you need. A good way to take over from Microsoft is to do everything Excell does that people need, and a few other things that they don't do.

Other packages you named are also open source, pick on that is closed to your needs, and make it better for everyone. There are plenty of out of work programers (like me - hint hint) who would love to modify an open source program for money.

Star/Open Office

[nelsonal]

I personally use and love both Open Office Calc, and MS Excell, but that's mostly for stats and financial data, which is their strong points. However, StarOffice 5 allowed you to create functions using C or C++, I can't recall which, its been too long since I looked at it. I don't know if its in the newest version but you might want to see if that meets your needs. It looked to be pretty powerful, more so than Excel's add-in functions. I was wanting to add Black-Scholes to it as a function, but gave up and just made it a sheet. Also Maple, once your used to the odd syntax of the command line, is excellent unless your doing lots of DSP type stuff, which Matlab excelled at. I haven't used Mathmatica, but everything I've seen or heard about it has been excellent, and I would like to try it if I had anymore heavy duty math to do.

Re:Spreadsheets

[topham]

The limit of 32,000 rows was removed. Just don't get me started at how it handles CSV files by default...

I'll second MathCad; not too expensive, either.

[alienmole]

I find MathCad really useful, even for day to day calculations. It supports units, so you can type in "5 mi/3 sec =" and ask for the answer in mph (or whatever). [If only NASA's Mars team had used it...]

It also licenses Maple (or parts thereof), so it can solve equations symbolically.

MathCAD is also the cheapest program of its kind. There are free tools, but none of them have GUIs like MathCAD, and dealing with complex mathematical formulae is one place where a GUI can make a big difference.

Re:Spreadsheets

[redcliffe]

For me input data isn't so much a problem, and neither is output data. I just want to be able to easily change values, and instantly see the results throughtout the calculations. Thanks

i guess

[eric6]

no one needs to remind you of brainfuck