Cell addresses in spreadsheets, by default are relative objects.  This means that when a formula is copied in Excel the relationship to adjacent cells that are included in the copied cell formula, also gets copied.  If the cell to the left is part of the formula in the cell being copied, the formula in the new location will automatically look for formula information in the cell to the left of the target cell (end point of copy).  Cell addresses reference columns and rows.  The cell at the intersection of Column B and Row 3, is labelled cell, B3.

My undergraduate students, at some point, were absolutely sure that Dr. Jenner was crazy about dollar signs ($).  The dollar sign, at least in Microsoft Excel, is an absolute reference anchor.  When cell formulas refer to a specific cell in a single relationship that is copied over many rows or columns, the absolute reference anchors fix the unique parameter that does not move.

Calculating a slope and intercept for a trend that is a simple linear regression, creates a unique slope and intercept for all the dependent (y’s) and independent (x’s).  In the actual forecasted trend, the same formula is copied across all the data points, but the unique slope and intercept parameters cannot move with the copied trend equation.  In this case, where the unique value is always fixed, a $ is place in front of both the Column address and also in of the Row.  The cell labelled B3, becomes $B$3, and wherever this cell address is copied in the worksheet it will always look in Cell B3 for the information.

This is one of my most powerful Excel tricks.   But over the years I have talked to some really gifted smart people that didn’t know this trick.

In my Greenville University Operations Management class by the end of the second week, students had submitted an assignment on building a multiplication table by coding the initial cell formula with the $ in the correct place.  And, it is a fact, the most excited individual in this classroom was Dr. Jenner.

Why?  I have changed the world with coding spreadsheets.

• In 1987, while recovering from a near-fatal farm accident with time on my (partially paralyzed) hand, I built a macro-driven accounting system with a chart of accounts in Lotus 1-2-3.  I bought a farm management spreadsheet add-in in 1985.  It was useless.  So typing with one hand, I built my own program.  Which is part of the reason I went back to graduate school.
• In 1995, I wrote my doctoral dissertation after I left the University of Missouri. I did much of the analytical work on my doctoral research in Quattro Pro.
• In 2011, I built and operated the Bioenergy Crop Adoption Model (BCAM) under Stephen Kaffka at the UC Davis’, California Biomass Collaborative.  I used all the normal PhD software tools like SAS, GAMS, and ArcGIS.  Before I moved back to Missouri in 2012, I programmed Excel’s Solver to get the same result.  In about 20 years, PhD analytics had made it to commercial spreadsheets.

For eight years as a faculty at Greenville University, I actively worked to arm our students with the powerful analytics in every commercial spreadsheet.  I am just a little crazy.  So that is legit.

In Week 2 of Operations Management, we built a multiplication table using absolute ($) and relative references (no $).  Then about Week 9, on the midterm, this multiple-choice question was asked.  In theory, every student had completed this assignment.  The score on this question was usually about 40 percent correct.  Sadly, when the exact same question was asked on the final, it still fell short of 100 percent correct.

The lower half of today’s image shows the difference between no $ and correctly applied $.  The values in the formula that reference Column A, require a $ in from of Column A, but not the row number.  The part of the formula that references the value in Row 2, needs a $ in front of the Row 2 reference, but not the column reference.  The formulas in the image in the upper left have no $.  They will fail to complete the table.  The formulas in the lower right of the image have the $ in correct order.  They will copy correctly from one cell to complete the entire table.

And, yes, the students did get this part correct.  Dr. Jenner is crazy about $ signs (in Excel).