In Chess, each piece has its own rules for moving on the board. Pawns can move just one square forward at a time. Rooks can move any number of squares in either vertically or horizontally. Bishops can move any number of squares along a diagonal path. One piece-- the Knight-- even has a weird rule for moving on the board. Then there are the royals: both can move in any direction. As long as the King isn't threatened, he can move in any direction one square at a time. Except for the weird path taken by the Knight, the Queen, has unrestricted movement on the board. So how are cell references like chess pieces? Cell references offer varying degrees of change when they are copied to different locations in a spreadsheet.
Cover Image made using HTML and CSS. Light edits made using MS Paint.
Additional images made using Excel 2003, with light edits made using MS Paint.
Chess pieces sourced from PNGkit.com
Cell References in Functions and Formulas
When we use functions by themselves or as part of formulas, we can use numeric data or text directly:
=4/8// Answer is 0.5=FIND("treasure","I heard there was buried treasure on Oak Island.")// The word "treasure" starts at character 26 of that sentence.
Normally, these values are in cells and we use references to those cells in functions or formulas instead:
=A10/A12// Given: Cell A10=4 and Cell A12=8, so answer is 0.5=FIND(H1,A1)// Given: H1="treasure" and A1="I heard there was buried treasure on Oak Island." The word "treasure" starts at character 26 of the sentence in A1.
Most spreadsheet users use Excel or a program modeled after Excel, so cell references are usually given by a column letter followed by a row number. However, there is a 3rd part of the cell reference which is optional, but it determines how functions and formulas are calculated when the are copied through a range of cells. This 3rd part is $ character, which is the anchor of a cell reference.
How are Anchors Used in a Cell Reference?
The big attraction of spreadsheets is being able to copy formulas across many rows and they all produce correct results. Cell references make that possible.
By default, cell references don't use anchors, and most of the time we don't need them. These are relative references.
Occasionally, however, we will need them for a formula to work properly. Anchors fix part of a cell reference to one row or a column, or both parts of a cell reference to locke the reference in place as the formula is copied to other cells. Sometimes we need 1 anchor, and sometimes we need 2 anchors. When we use 1 anchor, sometimes we need to anchor the column, sometimes the row. How do we know where the anchor goes or how many to use?
How Chess Pieces Move
The opening parargraph gave the basic rules of Chess. A discussion about chess tactics and strategies is best found elsewhere, but here is a review of how the pieces move:
- Pawns usually move just one square forward at a time.
- Rooks can move any number of squares in either vertically or horizontally.
- Bishops can move any number of squares along a diagonal path.
- Knights travel 2 squares before turning to travel 1 square (an "L" pattern)
- Under normal circumstances, the King can move in any direction one square at a time.
- Except for the "L" path taken by the Knight, the Queen, has unrestricted movement on the board.
Cell references also have their own rules for change as they are copied over a range. Below is a graphic showing cell references in terms of Chess pieces on a board, followed by a table comparing cell references to relevant Chess pieces:
Click the image for a full-size view
| Cell Reference | Reference Type | Anchors | Chess Piece | Default Degree of Movement |
|---|---|---|---|---|
$B$7 | Absolute | 2 | King | None if in check; 1 square in any direction |
$A1 | Mixed | 1 | Rook | Entire Column; No Rows |
H$5 | Mixed | 1 | Rook | Entire Row; No Columns |
C3 | Relative | 0 | Queen | Unresticted-- any number of squares in any direction |
When Should Anchors Be Used?
Anchors-- $-- get placed before a column letter or before a row number. Whether it's 2 anchors, 1 anchor, or even 0 depends on the situation.
Relative References (0 Anchors)
If a table uses the same formula both across columns and down rows, relative cell references can be used. Since spreadsheets normally use relative reference by default, there's nothing to think about.
Absolute References (2 Anchors)
If there is a special value which needs to be used by many cells (or even every cell) in a table-- for example, the number of grams in one troy ounce of silver; the real-time price of Bitcoin as provided by a feed; the exchange rate between the Euro (EUR) and Swiss Franc (CHF); the domain portion of a URL-- then this value is placed in one cell and referred to in all table data cells with an absolute reference. One value affects many table cells or every table cell.
| One Common Use of Absolute References |
|---|
Although they won't be be explained here, named ranges allow formulas to be entered using descriptive names rather than cell references. When a named range is created, the spreadsheet assigned to the name of the range a series of absolute references. This allows for descriptive formulas such as =(GrossPay-Taxes) instead of =(F15-F18). |
Mixed References (1 Anchor, Column or Row)
When a formula is copied to every cell within one column, and the formula changes are limited to each cell in that table column, the column letter of the cell refence gets anchored.
If something similar happens but across the row of a table, then the row number of the cell reference gets anchored.
| One Common Use of Mixed References |
|---|
| Collapsable ranges are ranges whose size changes depending on the formula's position within a column or row. This is handy when determining if an ATH for a cryptocurrency was reached after N days. One column has the prices, another column compares the other. In this situation, the column letter is anchored while the row number is allowed to adjust as it's copied down the column. |
Just My Two Sats
Just as Chess pieces have their own rules for movement on a board, cell references work differently depending on how they are anchored (using the $ character).
By default spreadsheet programs make references relative, and nothing special needs to be done. This is why relative references use 0 anchors.
Absolute references are used when the value at one location needs to be appled to a wide number of cells in a table across columns and down rows. Conversion factors and exchange rates are good for absolute references. Absolute references need 2 anchors. One special situation where absolute references are common is named ranges.
Mixed references use 1 anchor so that one dimesion is fixed while the other can adjust as needed when copied to other cells in a table. An anchored column allows the row numbers to adjust when copied. An anchored row allows columns to adjust when copied throught a row. One special situation where mixed references are common is a collapsable range.
| ● If you liked this post, please give it an upvote. ● If you liked this post but it's past the 7-day payout window, please tip using PIZZA, BEER, or the engagement token of your choice (or just send something from your account to mine). ● Please reblog or re-Hive this post if you found it useful. ● If there are corrections or clarifications I need to make concerning post content, let me know in a comment so I can update the post and give you credit for the correction. ● As I am neither a certified professional accountant nor a licensed broker, posts concerning cryptocurrency, commodities, securities, and money are presented for informational purposes only; DYOR. |
|---|
| ● If you're new to cryptocurrency and want to know what people think, feel free to drop by the Daily Crypto Markets Live Blog at LeoFinance. It's a free-wheeling text conversation comment thread that takes place in (more or less) real-time. ● Although I've learned a lot about Hive and its ecosystem of dApps, there's much more I don't know. Where can Hivers new and old learn more? Visit TheTerminal for FAQs, tips, references, and terrific how-to content. ● Did you know that there are 156 dApps built by Hive community members, and more to come? Discover them at Hive Projects. ● If your voting power is beneath dusting threshold, follow and use @dustsweeper and @dustbunny to boost your voting power. Authors you upvote will be glad you did, and you'll be happy to start claiming curation rewards. |
| Sometimes I discover posts way after the initial earnings period. It's too late for me to upvote posts then, so to make up for the lost upvote I send a slice of PIZZA instead. My main focus is cryptocurrencies from a number of angles (educational, commentary, observations, even pop culture). A secondary focus is sharing my discoveries about the world of Hive Social. Most of my posts can be found at these two locations: ● LeoFinance : @magnacarta ● Proof of Brain : @magnacarta Follow me at this D.Buzz-only account : ● D.Buzz : https://blog.d.buzz/#/@magnacarta.buzz-- note the ".buzz" at the end! If you follow me at @magnacarta.buzz, please follow me at this account as well I expect 2022 to be the year I go from being crazy or weird to being eccentric. If that doesn't happen, then Festivus will last longer than I can stand. |