# Excel as Analysis Tool and Homework Aid

• hotvette
In summary: Excel has a lot of built-in math and engineering functions, matrix functions, and an analysis tool pack that includes optimization tools. Some examples of what can be done with Excel include solving for roots of single variable equations, least squares fitting, solving sets of simultaneous non-linear equations, inverting matrices, and using drawing tools for basic motion tutorials. The built-in macro language is also a powerful tool for automating spreadsheet operations, creating custom worksheet functions, and writing general purpose programs. Despite its limitations, Excel is a valuable educational tool for physics, math, and engineering.
hotvette
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).

#### Attachments

• Newton Raphson.gif
41.9 KB · Views: 801
Last edited:
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.

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.

Dr Transport said:
I use something different for final reports and important presentations.

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.

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

Last edited by a moderator:
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.

hotvette said:
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).

hi, I would like to build up a Newton-raphson model by VBA, may i get a sample?

You should also take a look at:

## 1. How can Excel be used as an analysis tool?

Excel can be used as an analysis tool by organizing and analyzing large sets of data with its various functions and features. It allows for sorting, filtering, and creating charts and graphs to visualize data trends. Additionally, Excel's statistical functions such as mean, median, and standard deviation can be used to perform data analysis.

## 2. How can Excel be used as a homework aid?

Excel can be used as a homework aid by creating and organizing data in a spreadsheet format. It can be used for tasks such as tracking grades, creating schedules, and performing calculations. Excel's formatting and conditional formatting features can also assist with organizing and presenting data in a visually appealing way.

## 3. What are some advantages of using Excel for data analysis?

Some advantages of using Excel for data analysis include its ease of use, flexibility, and ability to handle large datasets. It also allows for quick and efficient calculations and data manipulation. Additionally, Excel's charts and graphs provide a visual representation of data that can aid in understanding and presenting findings.

## 4. Can Excel be used to analyze non-numerical data?

Yes, Excel can be used to analyze non-numerical data by utilizing its text functions such as CONCATENATE and LEN to manipulate and analyze text data. It also has features such as pivot tables and conditional formatting that can be used to analyze non-numerical data.

## 5. How can Excel be used to check for errors in data?

Excel can be used to check for errors in data by using its built-in auditing tools such as the Trace Precedents and Trace Dependents functions. These tools allow for identifying and tracing formula errors in a spreadsheet. Additionally, Excel's data validation feature can be used to set rules and restrictions on data entry to prevent errors.

• Computing and Technology
Replies
1
Views
988
• Programming and Computer Science
Replies
8
Views
718
• Other Physics Topics
Replies
1
Views
3K
• Set Theory, Logic, Probability, Statistics
Replies
4
Views
1K
• STEM Educators and Teaching
Replies
5
Views
790
Replies
2
Views
966
• Engineering and Comp Sci Homework Help
Replies
5
Views
1K
• Computing and Technology
Replies
10
Views
2K
• Linear and Abstract Algebra
Replies
1
Views
1K
• Electrical Engineering
Replies
5
Views
1K