Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Excel as Analysis Tool and Homework Aid

  1. Oct 10, 2005 #1

    hotvette

    User Avatar
    Homework Helper

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

    Attached Files:

    Last edited: Oct 10, 2005
  2. jcsd
  3. Oct 10, 2005 #2

    FredGarvin

    User Avatar
    Science Advisor

    I agree 100%. Just like any other tool though, you do need to know limitations. I use excel a lot.

    I am not a huge fan of some of their built in functions like it's data analysis pack's histogram function.
     
  4. Oct 10, 2005 #3

    Dr Transport

    User Avatar
    Science Advisor
    Gold Member

    We in industry use it extensively. I have written more VBA's than I care to admit. If you cannot run Excel very efficiently, you'll not last very long after getting your degree. Some of my co-workers are taking graduate engineering courses where ALL the assignments have to be done using Excel & VBA to calculate and plot the data. I am not a fan of the plotting routines, but they are useful for 1st order plots, I use something different for final reports and important presentations.
     
  5. Oct 13, 2005 #4

    hotvette

    User Avatar
    Homework Helper

    Just curious what you use for plotting.

    Also, if you've ever had the need (or desire) for high precision calcs, you should check out xNumbers. The user guide is excellent - very well done. It is an impressive package. I stumbled on it while searching the internet for clever algorithms for extended precision routines I was writing in VB (for fun, of course). The basic math routines in xNumbers were 10x faster than the ones I wrote, so I threw away 3 months of work and just used the routines from xNumbers.
     
  6. Oct 13, 2005 #5

    Dr Transport

    User Avatar
    Science Advisor
    Gold Member

    I have tended to go to LaTeX for the documents and gnuplot for the plots at home, I do not care for any of Excel's plotting routines, they do not look very professional. If possible copy the output to a text file, plot it up then reimport the plots back into Powerpoint while at work (my empoloyer will not allow me to install other office suites on my computer for 2 reasons, they are worried about licensing and corporate compatability). I have just gained access to TechPLot on my server so that is a step in the correct direction and running on a unix server, I can batch the plots (a couple of years ago I ran data plots on about 6 gigs of data in less than a day using TechPlot and a sed/awk script).
    I have been using this book lately for some Excel analysis, it seems to be fairly decent.
    http://www.oup.com/us/catalog/general/subject/Chemistry/AnalyticalChemistry/?view=usa&ci=0195152751
     
  7. Oct 16, 2005 #6

    dlgoff

    User Avatar
    Science Advisor
    Gold Member

    I once used excel/VBA to capture data from the computers serial port and put it directly in a spread sheet. The port was interfaced to an analytic balance where pipetts were being calibrated. The spread sheet used statistical calculations to determine and provide a report for a pass or fail

    Regards.
     
  8. Apr 1, 2009 #7
    hi, I would like to build up a newton-raphson model by VBA, may i get a sample???
     
  9. Apr 1, 2009 #8

    mgb_phys

    User Avatar
    Science Advisor
    Homework Helper

    You should also take a look at:

    [​IMG]
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?



Similar Discussions: Excel as Analysis Tool and Homework Aid
  1. Excel summing (Replies: 3)

  2. Excel + Calculations (Replies: 9)

Loading...