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