The previous post covered functions and formulas. Normally they are used in the grid that's on screen. However, functions an formulas can be used in a number of behinf the scenes tools of spreadsheet programs such as Excel and its imitators. One such tool is Conditional Formatting, so Part 3 for this 3-part post covers Conditional Formatting for the spreadsheet behind the D.Buzz Character Count Post Form for Buzzes (and Tweets).
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 |
Conditional Formatting
Normally, formatting cells and text is a one-and-done deal. However, there are situations where it makes sense for formatting to adjust to changing input:
- BTC prices appear green when they rise, and red when they fall;
- HIVE price appears in either bold or reverse video when it reaches an ATH;
- HBD price changes to yellow text on red background when it falls outside its +/- 0.050 margin.
In the case of these cryptocurrencies, formatting changes show how well or badly they are performing at any moment. Inside a spreadsheet, Conditional Formatting makes these formatting changes possible.
Click the image for a full-size view
Usually, Conditional Formatting is available from the Format menu. It can vary from version to version or even from Excel to another program. Be sure to select the range of cells needing to be formatted using Conditional Formatting first before accessing the menu.
The Conditional Formatting dialog box will appear:
Click the image for a full-size view
At least one condition is waiting to be defined. Some versions or programs have two or three on screen. Excel 2003 is limited to 3 conditions; later versions allow more conditions.
Each condition needs three things to work properly:
- Condition basis -- Is the condition based on a cell value, or is it based on a formula?
- The actual conditions -- either value-based conditions or the actual formula defining the condition; and
- Formatting for the condition being defined.
Above is a composite image showing the various forms of the Conditional Formatting doalog box used in Excel 2003: by formula and by cell value.
About Formula-Based Conditions
As noted earlier, the formulas used in Conditional Formatting are written to affect how the formatting should take place. They don't need to be related to calculations made on the spreadsheet itself.
If a formula is used for an either-or situation, then the same formula is used for both conditions but with the final result changed. These are a few examples:
- Equalities or inequalities;
- Milestones reached or missed;
- Odd or even;
- Yes or No.
The D.Buzz Character Count Post Form uses formula-based conditions. I used a formula because the formatting is also done on empty cells for cosmetic reasons. All conditions based on cell value can be written as formulas. Not all formulas can be used for conditions based on cell value.
About Cell-Based Conditions
When setting up Conditional Formatting based on cell values, there are more options from which to chose. It can be for a specific value, or it could be for a range of values, or it could exclude certain values. Just select the options and then provide the values.
Keep in mind that only the cell containing the values to be formatted conditionally can use Conditional Formatting by Cell Value. This is OK in many situations, but there will be situations where other cells need to be formatted conditionally based on a value several rows or columsn away. For those situations, formula-based conditional formatting would be better.
Potential Improvements
Currently, the counts change only after data entry is done. To see the counts change in real-time will require genuine form controls. I didn't have time to use them before when I made this quick-and-dirty form. A more polished version of this spreadsheet can include this feature.
Providing an option to night mode can also be done. As simple as this option seems, it will require added complexity. Then again, this is where Conditional Formatting can be used with extra conditions for the AND() function used in the Formula field.
Just My Two Sats
Normal cell formatting in a spreadsheet is static, one-and-done, format-and-forget. Conditional Formatting is formatting which adjusts to changing conditions as defined for the affected cells. Examples include changing colors or fonts (or both) for price changes as well as milestones or choices made.
Conditional Formatting takes place inside the Conditional Formatting dialog box. This dialog box is accessed from the Format menu and then the Conditional Formatting... option.
If Conditional Formatting is going to apply to a cell whose value can change, then it can use conditional formatting based on either the cell value or a formula. If Conditional Formatting is going to apply to a cell whose that is different from the cell whose value changes, then formula-based conditional formatting must be used.
If formula-based conditional formatting is going to be used, the formula is written in terms of the cell's formatting, not neceaasrily the calculation used in the grid-portion of the spreadsheet.
Although the D.Buzz Character Count Post Form works well, it can be improved in a number of ways. The most important upgrade would be the inclusion of genuine form controls in order to provide real-time changes in character counts. Other improvements would be providing a dark-mode option, although that will require extra complexity and additional 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. |
|---|
| ● 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. |