- #1

- 996

- 5

If you are in need of an analysis tool and/or homework aid, you may not realize that you probably already have an extremely powerful general purpose tool at your disposal - Microsoft Excel. I don't work for Microsoft nor am I even much of a fan of theirs, but I've found it an extremely versatile application. My only point is, don't discount it as a valuable tool.

It is pretty low level, to be sure. If you want to plot a function, you need to use the spreadsheet to calculate the x-y values, then plot it. But, this is extremely easy to do. It has a lot of built-in math and engineering functions, matrix functions, plus an analysis tool pack that includes optimization tools (e.g. goal seek, solver). Following is a partial list of what I've been able to do with Excel

- Solve for roots of single variable equations using Newton-Raphson and plot the convergence of the answer (see thumbnail below).

- Least sqaures fitting, using the built in functions or doing it from scratch (simple set of simultaneous linear equations)

- Solve a set of 7 simultaneous non-linear equations using Newton-Raphson. Requires setting up a matrix of partial derivatives and iterating on the solution. The built in macro capability comes in handy.

- Invert a 50 x 50 Hilbert matrix using a freeware add-in called xNumbers that performs calculations w/ variable precision up to 200 significant digits.

- Drawing tools. Not very sophisticated, but you can draw rectangles, circles, elipses, arrows, lines, etc. Not precisely, but can be very useful. I recently created a tutorial for the physics of basic motion and used Excel drawing tools to create all of the free body diagrams.

- The built-in macro language (Visual Basic) is a very powerful object oriented-ish programming language. It can be used to automate spreadsheet operations (e.g. driver for iterative Newton Raphson solution for the 7 simultaneous equations), create custom worksheet functions (for fun I wrote my own matrix inversion routine), and write general purpose programs that have nothing to do with Excel itself (except, perhaps, to use the spreadsheet cells as an input/output mechanism). A couple of examples I've done is creating stack and queue data structures using C-ish pointers, and extracting web content and parsing html. You can also use the macro language to perform SQL-like queries against databases and actually create your own databases. The databases you can query can be as simple as a CSV file, an Excel file (yes, it can be used as a database), or real databases like Access, Oracle, etc.

My contention is that the low level nature of the tool is actually a positive thing as an educational tool for physics/math/engineering. You are forced to set up the equations yourself (and therefore know what you are doing).

It is pretty low level, to be sure. If you want to plot a function, you need to use the spreadsheet to calculate the x-y values, then plot it. But, this is extremely easy to do. It has a lot of built-in math and engineering functions, matrix functions, plus an analysis tool pack that includes optimization tools (e.g. goal seek, solver). Following is a partial list of what I've been able to do with Excel

- Solve for roots of single variable equations using Newton-Raphson and plot the convergence of the answer (see thumbnail below).

- Least sqaures fitting, using the built in functions or doing it from scratch (simple set of simultaneous linear equations)

- Solve a set of 7 simultaneous non-linear equations using Newton-Raphson. Requires setting up a matrix of partial derivatives and iterating on the solution. The built in macro capability comes in handy.

- Invert a 50 x 50 Hilbert matrix using a freeware add-in called xNumbers that performs calculations w/ variable precision up to 200 significant digits.

- Drawing tools. Not very sophisticated, but you can draw rectangles, circles, elipses, arrows, lines, etc. Not precisely, but can be very useful. I recently created a tutorial for the physics of basic motion and used Excel drawing tools to create all of the free body diagrams.

- The built-in macro language (Visual Basic) is a very powerful object oriented-ish programming language. It can be used to automate spreadsheet operations (e.g. driver for iterative Newton Raphson solution for the 7 simultaneous equations), create custom worksheet functions (for fun I wrote my own matrix inversion routine), and write general purpose programs that have nothing to do with Excel itself (except, perhaps, to use the spreadsheet cells as an input/output mechanism). A couple of examples I've done is creating stack and queue data structures using C-ish pointers, and extracting web content and parsing html. You can also use the macro language to perform SQL-like queries against databases and actually create your own databases. The databases you can query can be as simple as a CSV file, an Excel file (yes, it can be used as a database), or real databases like Access, Oracle, etc.

My contention is that the low level nature of the tool is actually a positive thing as an educational tool for physics/math/engineering. You are forced to set up the equations yourself (and therefore know what you are doing).

#### Attachments

Last edited: