A high school physics teacher was asking questions in the Excel programming newsgroup about his VBA procedure that accepted input values (time, position, velocity, and acceleration) from the workbook, calculated values of position and velocity vs. elapsed time, and charted the results. I answered a couple of his VBA questions, but I suggested that he change his approach to use worksheet formulas. I decided that it would be a good blog topic, so here goes.
Excel simulation of 1-D motion
I followed the pattern that all good projects should follow. The project is divided into three parts: data input, calculation (often called business logic), and display. In a large project, these parts should be modularized, that is, separated into separate worksheets or workbooks, but in this kind of simple project, a blank row or column is enough. It’s also important that these sections follow logically from one to the next. In this project, the flow is left to right. The data entry section is the two leftmost columns; the calculation section is three columns in the middle; and the display section is two charts to the right.
The data input or data entry section of this project is located in the top left of the worksheet, and is shown below. The cells which are shaded yellow are the only ones in which the user is expected to enter values, and in fact they are the only unlocked cells in the protected worksheet (no password), so the shaded cells are the only cells which the user is allowed to change.
Each input cell has a clear label, so the user knows what to enter. The Maximum Time entry cell (B11) also has a comment to help the user enter a valid number. The allowed maximum is shown in cell B12, and is defined by the following formula:
=500*$B$8+$B$2
There are 500 rows of calculated results, so the maximum allowed value is the minimum time (B2) plus 500 times the increment (B8). I have not applied any formal data validation to the inputs, but I use conditional formatting to warn the user if the maximum time is not valid.
I used a formula that is True if the maximum entered is greater than the maximum allowed (B11>B12) or if the maximum entered is less than or equal to the minimum entered (B11<=B2). If this condition is met, I change the cell shading from light yellow to light red, and I change the font to bold italic red, as shown below.
The top 20 or so rows of the logic section is shown below; it extends downward for 500 rows. The logic section also doubles as part of the output section. This section is driven by formulas.
Cell D2 contains the initial time, which is defined by this formula:
=$B$2
Cell D3 contains the previous time plus the time increment. In addition, to prevent plotting of data beyond the maximum time entered, the formula checks that the calculated time exceeds the maximum time, and if so, the cell contains the #N/A error, which is not plotted in a line or XY chart. This formula is filled down all 500 rows of the calculation range.
=IF(D2+$B$8>$B$11,NA(),D2+$B$8)
Column E computes instantaneous position as a function of initial position, initial velocity, acceleration, and time, using the formula Isaac Newton first presented in my freshman physics class:
x = xo + vo t + 1/2 a t2
In Excel the formula in cell E2 is written as this and filled down 500 rows:
=$B$3+$B$4*D2+$B$5/2*D2^2
Column F computes instantaneous velocity as a function of initial velocity, acceleration, and time, using another of newton’s formulas:
v = vo + a t
In Excel the formula in cell F2 is written as this and filled down 500 rows:
=$B$4+$B$5*D2
If the time in a given row is replaced by #N/A, these last two formulas replace position and velocity with #N/A. I don’t mind keeping these values in the data range, so I can see instantly why the chart displays what it displays. However, for a nice demo like this, we should hide the errors. This is easy enough with conditional formatting: select the entire range, with D2 as the active cell, and invoke the Format menu > Conditional Formatting command. Choose Formula Is for Condition 1, andd eenter this formula:
=ISNA(D2)
ISNA() is the test for the #N/A error, and we use the relative reference D2 (no dollar signs) so the conditional formatting formula for any cell refers to that cell. We choose a format of white text, so the error is present in the cell but hidden from view.
The display section of the model consists of two XY charts, each plotting a single series. Each chart uses the data in column D as its X values. The chart showing position uses the data in column E as its Y values, while the chart showing velocity uses the data in column F as its Y values. The charts display all numerical data in the calculation range; the cells contaaining #N/A are part of the data range, but not plotted.
A zipped workbook containing this model can be downloaded from this link:
Physics_time_position_velocity_accel.zip
dermot says
Jon, it might be slightly less effort to create dynamic ranges for the chart series, and get them to use only the number of valid time rows.That would remove the need to police (and format) the output area for invalid times.
Jon Peltier says
Dermot – That’s a viable alternative. I don’t know what’s less effort. The basic workbook took me 20 minutes to put together. I thought of NA() in the chart source data before I thought of dynamic chart source ranges. I suspect the dynamic names would have taken me a similar length of time.
I think the concept of not plotting #N/A might be somewhat easier for an inexperienced Excel user than the concept of a dynamic range name based on worksheet ranges. A Stephen Bullen-type ChtFrmla defined name is conceptually much more difficult to understand, technically more difficult to construct, and much harder to debug and maintain than a worksheet-based name.
The model as I’ve fashioned it would be more robust, if someone decided to insert a copy of the model worksheet into the same workbook. If the dynamic names were defined on a workbook level, the copied chart would refer to the name defined on the original sheet, not on the copied sheet. If the dynamic names were defined on a worksheet level, the chart on the copied sheet would contain hard-coded values.
Doug Jenkins says
Jon – one thing that bugs me is that schools and universities seem to have an aversion to using spreadsheets for science and maths. It’s almost like they thing a spreadsheet is for financial analysis and nothing else.
I think your model is an excellent introduction to the sorts of things you can do with spreadsheets in science education. Also I totally agree with your “keep it simple” approach, whilst not forgetting the basics of good design; i.e separating data, analysis, and output.
Jon Peltier says
Doug – Thanks for your comment. That’s a good point. Maybe people hear the horror stories about statistics done in spreadsheets, or the $100MM errors that bankrupt companies. Or whatever.
My first engineering-based programming I did was to reconstruct a model that took input from the user (temperature etc.), looked up materials parameters from a database (an Excel worksheet in my case), and spit out predicted values for mechanical behavior. I wrote the program in the old XLM language to automate Solver, because we had a value the model output and we needed the corresponding input. Manually it took two days to produce a dozen output values, and the program did it in a couple minutes. So it was a big deal. Up until I wrote the program, I had to submit my conditions to the engineers in another division and wait for them to process the values for me.
My colleague at the division noticed I’d stopped asking them for calculations and wondered how I got mine. I sheepishly admitted it was “only” an Excel solution. I was surprised to learn that they used Excel all the time for engineering work, because I’d envisioned large mainframes (Crays, really) to do the work. They liked my system and implemented it themselves. My first VBA project was to upgrade my XLM materials properties program.
Jon Peltier says
I should point out Doug’s blog, Newton Excel Bach, which looks at scientific and engineering applications of Excel.
Doug – Maybe I’ve become blind, but I couldn’t find the “about” page on your blog.
Doug Jenkins says
Thanks for the plug Jon (and in the competition thread!)
You’re not blind, I can’t find the about page on the curent layout either. I’ll probably switch to a different design when I get time.
Jon Peltier says
Doug – I thought I had seen the About page once before while reading your blog. These templates are so detailed and convoluted, it’s hard to make them do quite what you want. I guess we all need a couple new acronyms: CSS and PHP.