Spreadsheets for Blogging: The Spreadsheet Behind the D.Buzz Character Count Post Form for Buzzes (and Tweets)-- Part 2

The previous post briefly covered simple data values, cosmetic features, and merged cells for data entry; It also went into somedetail on named ranges. So Part 2 for this 3-part post is about the functions and formulas used by the spreadsheet behind the D.Buzz Character Count Post Form for Buzzes (and Tweets) covers functions and formulas.

^{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.}

Post Navigation

Post Part	Topics Covered
Part 1	Behind the Scenes; Elements; Simple data values; Cosmetic features; Merged cells for data entry; Named ranges
Part 2	Functions and Formulas
Part 3	Conditional Formatting; Potential Improvements; Just My Two Sats

Functions

Functions are routines in Excel and other spreadsheet programs which perform an action which produces a result. SUM() takes numbers and adds them to produce a sum. TODAY() takes a special number or special text string to return the current day's date.

The D.Buzz Character Count Post Form uses functions in 6 locations:

• the right end of each status line;
• the left end of each status lines;
• Conditional Formatting for each status line.

Let's start with the right end of each status line:

LEN()

How does a spreadsheet know how many characters are in a field such as formTitle or formBody? The field is considered one piece of text, and the length of this piece of text is counted. LEN() is the function used to measure the length of a piece of text.

• LEN(formTitle) returns the number of characters in formTitle.
• LEN(formBody) returns the number of characters in formBody.

The following uses of LEN() are OK:

Example	Value Passed to LEN()
LEN($G$6)	Cell Address
LEN(formTitle)	Named Range
LEN("ETH fees are too damn high!")	Text String
LEN("ETH fees are too " & wordProfanity & " high!")	The combination of a text with with a named range plus another text string
LEN("That ETH TX cost me " & SUM(62,38) & " GWei.")	Text plus a function plus more text

Whatever is passed to LEN() can be as simple or as complex as needed, but in the end LEN() will produce a number (or an error message, but that's a topic for another day).

Next is the left end of the status line, where 2 functions are used before a piece of finishing text: ABS() and IF()

^{Click the image for a full-size view}

^{Click the image for a full-size view}

ABS()

The ABS() function returns the absolute value of a number. If a number is positive, its distance from "0" is positive. If a number is negative (less than zero), it's distance from "0" is also positive. This is shown in the table below:

Number	ABS()	Absolute Value
69	ABS(69)	69
+42	ABS(+42)	42
0	ABS(0)	0
-38	ABS(-38)	38

Originally an IF() was going to be used to handle positive and negative numbers for display; If positive, display the number as-is, but if negative then the "-" character is added before the number. While doable and usable, it was cumbersome. Using ABS() is an elegant solution to this problem.

^{Click the image for a full-size view}

IF()

As shown in the previous image, the left section of the status lines is made of 3 parts:

1. The absolute value returned after using the either the name fxRemainingTitle or fxRemainingBody;
2. Depending on the value of fxRemainingTitle or fxRemainingBody, either the word remaining or extra;
3. Text to finish the status line.

While #1 returns one value no matter the input, and #3 is just a fixed text string, it is #2 which can change depending on the value of either fxRemainingTitle or fxRemainingBody.

This is the IF() used in #2 of the status line formulas:
IF(fxRemainingTitle>=0," remaining"," extra")
or
IF(fxRemainingBody>=0," remaining"," extra")

If either fxRemainingTitle or fxRemainingBody return a positive number or zero, then use the word "remaining" with a leading space. However, if either name returns a negative number, then use the word "extra" with a leading space.

Spreadsheet Tip:
The IF() function is set up this way: IF(test,true,false). Whichever situation is more likely to occur should be tested for TRUE. This way, the function stops and doesn't need to test for FALSE. Only if the test proves FALSE does the FALSE part of the IF() function get executed. When a spreadsheet uses the same IF() over many, many cells, the time savings will be noticeable.

Conditional Formatting is the final place where functions are used, and it uses the AND() function.

AND()

As powerful as IF() functions are, they suffer from one weakness: They can only test 1 condition. Even if nested IF()s are used, the test portion only tests 1 condition.

One way to test multiple conditions at the same time is to use the AND() function. Sine these conditions affect formatting, the place to use the AND() function is the Conditional Formatting dialog box:

^{Click the image for a full-size view}

To show the status line in red text on white background, these were the AND() functions used for Condition 1:
=AND(fxRemainingTitle>=0,fxRemainingTitle<=5)
and
=AND(fxRemainingBody>=0,fxRemainingBody<=10)

Unlike IF(), the AND() function can test as many conditions as needed. If every condition inside the AND() function tests TRUE, then the formatting takes place as specified. If even 1 test inside the AND() function fails or results in FALSE, then nothing happens and formatting remains normal.

Although I haven't used AND() in this way for this situation, it can be used inside an IF() function to provide multiple tests which need to be TRUE.

Note that the formulas to define conditions used for Conditional Formatting are made strictly to affect the formatting of a cell. They don't need to be related to what happens functionally in a spreadsheet.

OR(): Companion Function to AND()
Just as there is an AND() function, Excel and other spreadsheet programs have an OR() function. Like AND(), the OR() function can test as many conditions as needed. Unlike AND(), only 1 condition needs to test TRUE before the OR() function returns TRUE.

Formulas

Formulas are just functions, numbers, and text combined in ways which produce results functions alone can't. They can be as simple as basic math (G3-G6) or as complicated as this INDEX-MATCH formula used for looking up an exchange rate from a table:
=INDEX($H$3:$H$10, MATCH(B6, $G$3:$G$10, FALSE))

Even text can be used in formulas:

• ="Hive is the premier blockchain for Web 3.0." // One sentence
• ="Hive is the " & "premiere " & "blockchain for Web 3.0." // One sentence, 3 strings
• ="Hive is the " & wordBest & "blockchain for Web 3.0." // One sentence, the name wordBest surrounded by text strings

The best examples of a formula are the dynamic status lines beneath the text and body fields for the D.Buzz Character Count Post Form. Here are the formulas in their entirety:
=ABS(fxRemainingTitle) & IF((fxRemainingTitle>=0)," remaining"," extra") & " characters in Title"
and
=ABS(fxRemainingBody) & IF((fxRemainingBody>=0)," remaining"," extra") & " characters in Title"

They may look difficult, but they are actually simpler pieces connected by the "&" character. The left piece is just the ABS() function being used. The middle piece is just an IF() function with a simple test and alternatives for testing TRUE or FALSE. The right piece is just text to complete the dynamic status lines.

Names fxRemainingTitle and fxRemainingBody are defined not as aliases for cell references but rather for simple subtraction calculations:

• fxRemainingTitle was defined as nMaxTitle-nTitle; and
• fxRemainingBody was defined as nMaxBody-nBody.

Just My Two Sats

Part 1 of this post focused on Named Ranges along with a few simpler elements. This part focuses on functions and formulas.

Functions are pre-made routines which are part of a spreadsheet program such as Excel. They take one or more values or references in order to produce one result. The right end of the dynamic status line uses a function.

Functions can be as simple as LEN() or ABS() which take one value or reference and produce one result each. Functions can be more complex such as IF() and AND() (and OR()) which take several values or references (including other functions). Simple or complex, functions give one result.

Formulas are combinations of functions arranged in ways which give a result functions by themselves cannot produce. The left end of the dynamic status line uses a formula (the ABS() plus an IF() plus a piece of text).

Usually functions and formulas are used on the spreadsheet grid in cells. However, they can be used in behind the scenes tools of spreadsheets such as Conditional Formatting. Part 3 of this post will cover Conditional Formatting.

● 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.

~~~~~ Public Service Announcements ~~~~~

● 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.

~~~~~ About @magnacarta ~~~~~

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.