Bitcoin's nickname is "The King of Cryptocurrency." Pelé is the alias for Edson Arantes do Nascimento. How are named ranges like nicknames? Just as people and things can be referred to by other names, so it is with cells, ranges of cells, constant values, even formulas in spreadsheets.
Cover Image made using HTML and CSS. Light edits made using MS Paint. Additional images made using Excel 2003, with edits made using MS Paint.
Nicknames, aliases, AKA's ("also known as" names), etc.-- whatever word we want to use, they are just names which are easier for us to remember than what they represent. Here are some famous nicknames:
| Nickname | Real name |
|---|---|
| Pelé | Edson Arantes do Nascimento |
| Charo | María Rosario Pilar Martínez Molina Baeza |
| J-Lo | Jennifer Lopez |
| The King | Elvis Presley |
| Lawrence of Arabia | Thomas Edward Lawrence |
| Neo | Thomas Anderson |
When it comes to cryptos, we use their symbols and names interchangeably even though we know there are distinctions between the two. Bitcoin is the nickname or alias for "the Bitcoin network," "the Bitcoin platform," and (more broadly) "cryptocurrency." We even use nicknames or aliases or our crypto accounts, and in this case we have good reason to do that.
Spreadsheets let us use nicknames, too. In Excel and its imitators, these names are called named ranges or simply names.
The Need for Named Ranges
In the early days of a spreadsheet meant to track data over time, it may be easy to remember that cell F5 contains, say, the latest closing price for Bitcoin (BTC) while H5 contains, the latest closing price for Hive (HIVE). After a while, when the spreasheet has closing prices over a few months and additional columns are added and the spreadsheet is handed off to a new analyst, it can be difficult to remember that F5 equals Bitcoin's latest closing price and H5 equals Hive's latest closing price.
Also, what if other crypto's latest closing prices are added for coverage? Say, Ethereum (ETH), Litecoin, HIVE-Backed Dollars (HBD), and Cub Finance (CUB)? Space needs to be found to place the latest closing prices for each othe these cryptos. It's possible that to accomodate these new arrivals, the originals Bitcoin and Hive need to be shifted to other cells. How easy will it be to remember the updates locations for BTC and HIVE then? It can be done, but it would be a hassle after a while. There must be a better way to handle this situation.
There's a Better Way: Named Ranges
Named ranges provide a way to refer to a cell, a range of cells, an alphanumeric value, even a function or formula by an alias or nickname or some other name. Here are some examples of named ranges and their descriptions:
| Named Range | Range | Description |
|---|---|---|
DQ | CHAR(34) | Double-quotes character rendered as text |
closeBTC | F5 | Latest closing price for BTC |
closeHIVE | H5 | Latest closing price for HIVE |
Ratio | (F5/H5) | The Bitcoin-Hive Ratio: How many HIVE in 1 BTC? |
minBTC | MIN(A:A) | The minimum closing BTC price, found in Column A |
maxHIVE | MAX(C:C) | The maximum closing HIVE price, found in Column C |
last7hive | C3:C9 | The last 7 closing prices for HIVE |
closeSatoshi | closeBTC/0.00000001 | closeBTC expressed as Satoshi |
btc100k | 100000 | One hundred thousand |
wordSatoshi | "Satoshi" | The text string "Satoshi" |
prevBTC | A3 | The previous closing price for BTC |
updown | IF( ((closeBTC-prevBTC)>0), "UP", IF( ((closeBTC-prevBTC)<0), "DOWN", "UNCHANGED")) | Determines if closing price went up, down, or stayed the same |
Named ranges such as closeBTC and last7hive can be made using either of two methods:
- the Name Box (usually near the Formula Bar);
- the Define Name dialog box.
The Name Box is great for creating names using selected ranges. When one of these names needs to be modified in some way. though, it's necessary to use the Define Name dialog box.
Certain names (not really ranges), are made using the Define Name dialog box exclusively:
- Constant or fixed values such as
DQ(a special character),wordSatoshi(a word), andbtc100k(a number); - Functions such as
minBTCandmaxHIVE; - Formulas such as
Ratio,closeSatoshi, andupdown.
As long as names appear in the Name List, they can be used by names based on more complex formulas as in closeSatoshi and the nested IF() function named updown.
Markdown was used to make the table above, then the names were added to the Define Name dialog box. This is what the Define Names dialog box in Excel 2003 looks like including the names from the Markdown table:
Click the image for a full-size view
Things To Note about Using the Define Name Dialog Box
Accessing the Define Name Dialog Box
Excel 2003 was used to make the screen captures, so references in this post are based on Excel 2003. Procedures will vary in other versions of Excel as well as in Excel imitators.
In Excel 2003, Clicking the Insert menu and then the submenu Name ► Define... brings up the Define Name dialog box.
Click the image for a full-size view
Appearance Varies with Program Version
Excel 2003 uses a simple Define Name dialog box, while later versions of Excel use a Define Name dialog box which looks more like tthe Markdown table. spreadsheet programs modeled after Excel have similar presentations of the dialog box.
Excel Assumes References Come from Whatever Worksheet Was Active
When F5 was entered as the definition for the name closeBTC, Excel not only converted the relative reference F5 into the absolute reference $F$5, it also specified that the reference came from the worksheet/tab Sheet1. Spreadsheet files-- "workbooks"-- can contain multiple worksheets, so Excel makes this specification for us. We can always change this to a different worksheet, but most of the time it's not a concern.
Editing a Name Definition Can Be Tricky
| WARNING: |
|---|
| When trying to edit the definition of a name, Excel thinks another reference needs to be added so it will go into edit mode to place it: |
 Click the image for a full-size view Surgical precision is needed to remove whatever needs to be removed, so place the cursor next to the unwanted character and backspace/delete characters one by one until the unwanted characters are replaced by necessary characters (or just left clean). |
How To Add a Name Using the Define Name Dialog Box
Below is a compsite image showing how to add a name using the Name Dialog box followed by a text description of the procedure:
Click the image for a full-size view
- In the field labeled
Names in Workbook, type the name of the new name. - Select the text inside the field labeled
Refers to:and delete it. - Type the new name definition.
- Click the
Addbutton.
If the name was defined as a relative range, it will appear as a relative reference. More importantly, it will work as a relative reference. If the name needs to be an absolute reference (or even a mixed reference), then add $ anchors as needed.
When the Define Name dialog box disappears, the Name Box will show the address of the active cell except when B1 is the active cell. If B1 is selected, the Name Box will show SalesTax, the name which was just added.
Just My Two Sats
Nicknames are names which are more memorable than what they represent. They aren't necessarily shorter than the original names, but they are more recognizable. Just as people and things have nicknames, spreadsheet cells or ranges of cells have nicknames too; these are named ranges, or simply names.
The Name Box and the Define Name dialog box are two ways to create named ranges (and names which don't refer to ranges). The Name Box is a quick way to create names for ranges, but editing a name created this way requires using the Define Name dialog box.
The Define Name dialog box is a more versatile tool for creating names since it also allows for the creation of aliases for fixed values, text strings, functions, even complex formulas such as a nested IF(). Once a name is created, it can be used by other name definitions to create more complex formulas.
Names are usually associated with the worksheet which was active at the time time name was created. Excel adds this worksheet prefix automatically to a name based on a cell reference.
After a name is created using either method, it will be available from the Name Box. For a name created as an alias for one cell, the Name Box will show the name of this cell when the cell is active instead of the usual cell address.
| ● 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. |