Logical Functions

Danny September 11, 2012 1 Comment

How to Dynamically Color-Code Dates with Conditional Formatting in Excel

Conditional Formatting for Dates

One of my viewers wants to learn how to “dynamically” color-code cells that contain dates. She is monitoring the frequency of contact with her clients and she wants Excel to automatically apply:

One background color to cells where it has been less than 60 days since last contact
A different color when the time frame is between 60 and 90 days
A third color when it has been over 90 days since the last contact with a client

Here, I demonstrate how she can do this using Conditional Formatting Rules. However, this will only work if you are using Excel 2007 or Excel 2010.

Understanding Dynamic Date Functions

In order to make this visualization “dynamic,” I make references to cells that contain the =TODAY() Function. This is a “volatile” function that updates automatically based upon the date in your computer system’s clock. I use this to classify the last date of contact with her clients.

Improvements in Conditional Formatting

Beginning with Excel 2007, you can now apply multiple rules that use formulas. Remember that when you use a formula, it must evaluate as “TRUE” in order to apply the formatting that you specify.

Formulas Used to Apply Conditional Formatting

Rules for Conditional Formatting

To make it easier for you to practice this skill, I have posted this table that details the formulas that I used for each of the three rules in my video tutorial.Note that with the middle condition, I use the =AND() Function. All logical tests must answer TRUE for the AND() Function to return TRUE.

It will take a little bit of practice for you to become comfortable with these formulas. As a Best Practice, I recommend that you create a table of formulas so that you can see how a “tweak” in your formula can turn a FALSE answer into a TRUE answer.

Watch this Video in High Definition

Follow this link to watch my Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Get the Best Tips and Training for Excel

Click on this link to learn more about my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” This link will take you to my secure online shopping website – http://shop.thecompanyrocks.com

Subscribe to my Video Podcast on iTunes

Here is the link to go to my free Video Podcast, “Danny Rocks Tips and Timesavers” on iTunes.

How to use Conditional Formatting to Color-Code Dates in Excel [ 8:49 ] Play Now | Play in Popup | Download (3143)

Danny September 5, 2011 Leave a Comment

How to Use Criteria in Excel Functions and Filters

Criteria for Advanced Filter

When you apply a Filter to an Excel data set, you use Criteria. In a filter, only the records that can answer “TRUE” to the criteria that you set will appear. The records that answer “FALSE” are hidden while the filter remains in place.

The IF(), AND() and OR() Functions belong to a group of Logical Functions. In their function arguments, they evaluate a “Logical Test” and can perform one action is the answer is “TRUE” and a different action if “FALSE.”

The AND() Function – each of the Logical Tests must be met in order to return the answer “TRUE.”
The OR() Function – if any condition is met, the answer that is returned is “TRUE.”
I frequently “Nest” an AND() Function or an OR() Function inside an IF() Function to test multiple criteria.

Advanced Filters in Excel

While many Excel users are familiar with Filtering, in my experience, I find that many people get confused when it comes to using Advanced Filters. In this video tutorial, I try to eliminate this confusion. Watch and you will learn how to:

Write Criteria for Advanced Filters using “AND” criteria and “OR” Criteria.
Copy the “Filtered Records” to another location
Use the Keyboard Shortcut Ctrl + Shift + L to quickly “toggle” normal filter drop-down menus on or off.

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Defintion on my YouTube Channel – DannyRocksExcels

Filed Under: Data Filters and Queries, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: Advanced Filter for Excel data, AND / OR Criteria for Filters, AND() Function, Criteria for Advanced Filter in Excel, Excel Keyboard Shortcuts, Excel Video Training, Filter a List, IF() Function, Logical Functions, OR() Function, Video lessons for Excel

Danny February 17, 2011 2 Comments

How to Find and Change Numbers Stored as Text in Excel

Whenever a client or a colleague sends me an Excel workbook I like to inspect it, to be sure that the “numbers are really numbers.” I will not get accurate results when I SUM them if there are some cells that appear to contain numbers, but really display a “number stored as text.”

Prevent GIGO

This is an example of GIGO – “Garbage in delivers Garbage Out!” I take steps to prevent an outbreak of GIGO in my Excel worksheets.

Looking for Numbers Stored as Text

As a best practice, I use both the COUNT() Function and the COUNTA() function to see if there is a difference between the number of cells with numbers (the COUNT Function) and the number of non-blank cells (the COUNTA Function). In this example, I was expecting to see 31 cells with numbers in the range of sales. There were on 27. So, I need to find those 4 cells that contain “numbers stored as text.”

If you have a keen eye – and some time to spare – you can look for cells with a little Green triangle in the upper left corner. Click on the message next to these “marked” cells and read the error message. Excel will offer to correct the error – one cell at a time. That is not an efficient way to proceed!

Use the ISTEXT() or ISNUMBER() Functions

An alternative way to find the cells that contain text stored as numbers is to use one of the “logical functions” ISTEXT or ISNUMBER. Each returns either TRUE or FALSE when evaluating a cell. You can then sort the data to group the TRUE results together and then change them – again, one at a time. This is also not the best way to make the changes.

Use Conditional Formatting to Highlight Cells with Number Stored as Text

I like to use the ISTEXT() Function as a “Rule” to apply special formatting to the cells that evaluate as TRUE with Conditional Formatting. Be sure that you select the cells that you want to evaluate before applying the Conditional Formatting.

Use Paste Special – Add

The quickest way to change numbers stored as text into real numbers is to use Paste Special – Add. First, select and copy a blank cell. Next, select your data range – in this case the cells with “Sales.” Then, right click the selection and choose Paste Special. In the dialog box select “Add.” Now – instantly – all of the cells with numbers stored as text have been changed into real numbers. You have stamped out GIGO!

Learn how to “Master Excel in Minutes – Not Months!”

Follow this link to watch this video in High Definition, Full-screen mode on my YouTube Channel – DannyRocksExcels

How to Find and Change Numbers Stored as Text in Excel [ 7:19 ] Play Now | Play in Popup | Download (1308)

Filed Under: Conditional Formatting, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: Conditional formatting in Excel, COUNT Function, COUNTA Function, Excel Video Training, GIGO, ISNUMBER Function, ISTEXT Function, Logical Functions, Number stored as text, Paste Special, Video lessons for Excel

Danny November 1, 2009 8 Comments

Build an Accounts Receivable Aging Report

Here is another request for help from a viewer. How to create a report that shows “the aging” of past due invoices (1 – 30 days past due, etc.).

Here are the Functions that I use in this report:

=IF()
=AND()
=WEEKDAY()

Looking for a specific Excel video? Take advantage of my Index of Excel Video Lessons to find the video that you want to watch.

Invitation to Visit My Online Shopping Site

Want to learn the best Tips for Excel? You can purchase my DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007

I use a secure shopping cart at http://shop.thecompanyrocks.com – I invite you to pay a visit today!

Click here to see a Listing of “The 50 Best Tips for Excel 2007”

Filed Under: Excel Financial Tools, Excel Functions, Excel Training, Video Response to Viewers Tagged: A/R Aging Report, Absolute cell reference, Accounts Receivable Report, Amount Past Due, Logical Formulas, Logical Functions

Danny September 18, 2008 1 Comment

The versatile and valuable =IF() Function

MS Excel’s =IF() Function is a very versatile function and it is used in a variety of situations. Watch this short video lesson to learn how to use it when Grading Scores, Calculating Shipping Rates and “Trapping Errors.”

Here are the steps to follow in this lesson:

The =IF() Function requires three arguments: A “Logical Test” – (which will return either a TRUE of a FALSE Response); the Value if “True,” and the Value if “False.”
In my first example I have set up a simple scoring system for grades. For the Value if TRUE, I have entered “Pass.” Notice that the TEXT must be enclosed in ” “.
In my second example, I have “nested” several =IF() functions inside the Value if FALSE Function Argument. You can “nest” up to 7 different IF() Functions in a formula.
Notice how useful the =IF() Function is when you want to “trap an error.” Especially when you are starting to build a spreadsheet and many cells contain Zeros or NULL Values.

Find the Excel Video Lesson that you want – Index to All Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Filed Under: Excel Functions, Excel Training Tagged: Conditional Functions, Excel Video Training, Formula to assign a letter Grade, Formulas to caluclate Shipping Rates, IF() Function, Logical Functions, Nested Functions, Trapping Errors

Danny Rocks “Training You to Excel”