How to use the IF function with relational operators with Excel in Microsoft and Office 365

As part of our series of helping customers with their small business needs Longmont Computer Physicians, LLC is offering these free classes on how to use different software programs. Here is our instructional video on using Microsoft Excel spreadsheets.

Microsoft 365 Beginner class – Excel
Microsoft 365 Intermediate class – Excel
Microsoft 365 Advanced class – Excel


 

The IF function in Excel is one of its logical functions, which evaluate to either a “TRUE” or “FALSE” value. The IF function in Excel lets you perform a logical test on a cell’s value and then return a result based on whether or not the cell’s value passes or fails the test. The IF function is similar to an “If…then…else” coding statement. You must know at least three different arguments to write a logical function. The first argument is the “logical test” to apply to the cell. The second is the cell value or formula to return if the test returns a “TRUE” value or “passes” the logical test. The third is the cell value or formula to return if the test returns a “FALSE” value or “fails” the logical test. The syntax of the IF function is: =IF(logical_test,true_response,false_response) If you want the formula to display a text value for the true response or false response, then you must place the text value inside double quotation marks (“ ”). If you want the function to display a date, it must be enclosed within pound signs (##). The only time you wouldn’t mark the data type of the value to return is if you want the function to display a numerical result or calculate a formula. Often, you may want to know if a cell passes or fails multiple logical tests.

One way to apply multiple logical tests to a cell is to use nested logical functions. A “nested” logical function in Excel is one that places the cell through a second logical test if it “fails” the first. These functions are useful for determining the value of a cell by placing it through several different tests, displaying different results based on which test it passes. You can nest up to 127 additional IF statements behind your original, if needed. The syntax for these are: =IF(logical_test _1,true_response,IF(logical_test_2,true_response,false_response)) You must remember to close all open parentheses for every IF statement you nest within the logical function at the end of the formula. In this case since there are two IF statements, there are two closing parentheses at the end of the formula. Alternatively, if using Excel 2019 or later or using Excel as part of Office 365, you can use the new IFS function to pass a cell though multiple logical tests and return a value for the test it passes. The IFS function replicates the features provided by nested IF functions, but uses a simpler, streamlined syntax. We’ll examine the IFS function in a later lesson.

In addition, you may also want to know if a cell meets multiple criteria at the same time. You can use the AND and OR functions to find this out. The AND function returns a “TRUE” value if the evaluated cell passes all the logical tests listed after the AND function. The OR function returns a true value if the evaluated cell passes any of the logical tests that follow the OR function. Note that you can evaluate up to 255 different logical tests after the AND and OR statements. When you look at how you can combine these tests with the IF function or nested IF functions, you can see how you can start to become a very powerful formula creator. Combining these Excel functions lets you place cells through a battery of tests, and then decide what function to perform or value to display, based on the results from the tests. The general syntax when combining the IF function with the AND and OR functions is as follows: =IF(AND(logical_test_1,logical_test_2,logical_test_3,etc.),true_response,false_response) =IF(OR(logical_test_1,logical_test_2,logical_test_3,etc.),true_response,false_response) The IFS function is only available in Excel 2019 or later or Excel as part of an Office 365 subscription. The IFS function in Excel lets you pass a cell through a series of logical tests and then return a value based on which logical test the cell passes. This provides a very similar functionality to using multiple, nested IF statements. When using Microsoft Office Excel 365.

How to create charts and graphs with Excel in Microsoft Office 365

As part of our series of helping customers with their small business needs Longmont Computer Physicians, LLC is offering these free classes on how to use different software programs. Here is our instructional video on using Microsoft Excel spreadsheets. 

Microsoft 365 Beginner class – Excel
Microsoft 365 Intermediate class – Excel
Microsoft 365 Advanced class – Excel

Excel lets you easily create charts from the data in a worksheet. Charts are useful for times when you want to create visual representations of the worksheet data for meetings, presentations, or reports. To insert a chart, select the cell range that contains the data for the chart. Be sure to also select the data’s adjacent row and column labels to automatically apply them to the chart, saving you the step of selecting them later. You can adjust your data selection later, if needed, but selecting the data first lets you see chart previews more clearly. Next, click the “Insert” tab in the Ribbon. In the “Charts” button group are the types of charts you can insert. Starting in Excel 2019, two new chart types appear in this button group. You can access the new “Funnel” and “Map” chart types by clicking them within their respective chart type drop-down buttons in the “Charts” button group on the “Insert” tab of the Ribbon. Alternatively, you can select them after clicking the “Recommended Charts” button in the “Charts” button group on the “Insert” tab of the Ribbon.

One way to insert a chart is to click the “Recommended Charts” button in the “Charts” button group on the “Insert” tab of the Ribbon to open the “Insert Chart” dialog box and display the “Recommended Charts” tab. This tab shows the types of charts Excel thinks would best illustrate your selected data. You can click the choices at the left side of the tab to see a preview of the chart appear to the right. To insert one of the chart choices into the worksheet, click it to select it in the listing at the left side of the tab. Then click the “OK” button at the bottom of the “Insert Chart” dialog box. Another way to insert a chart based on your currently-selected data is to click the button that represents the general chart type to insert within the “Charts” button group on the “Insert” tab of the Ribbon. Then click the specific chart subtype to insert in the button’s drop-down menu. To view all the chart type choices and then insert a selected chart type, click the “See All Charts” button in the lower-right corner of the “Charts” button group to open the “Insert Chart” dialog box. To show all the available chart choices, click the “All Charts” tab. On this tab, you can select a major chart type from the listing at the left side of the dialog box. You can then select the specific subtype to insert by clicking the desired subtype in the list at the right side of the dialog box. To then insert the chart of the selected subtype, click the “OK” button at the bottom of the dialog box. Using any of these chart insertion methods inserts a chart of the selected subtype as an embedded chart object in the current worksheet. The next thing to note is that when a chart object is selected, a new contextual tab then appears in the Ribbon.

This is the “Chart Tools” contextual tab and it consists of two tabs, “Design” and “Format.” You use the buttons in the various button groups on these two tabs within the “Chart Tools” contextual tab to change the selected chart objects. When a chart is selected in Excel, a two-button or three-button grouping of chart options appears at the right side of the selected chart, depending on the chart type you inserted. The buttons are, from top to bottom, “Chart Elements,” “Chart Styles,” and, optionally, “Chart Filters.” You can also use these buttons to change your selected chart. When you insert a new chart into a worksheet, the entire chart is initially selected. The “Chart Tools” contextual tabs then appear in the Ribbon. Two or three drop-down buttons then also appear at the right side of the chart. When editing charts, the first task with which to familiarize yourself is selecting chart elements. Note that a chart is not a single object, but rather, is a complex object comprised of many smaller, selectable objects. You must know exactly which chart element is selected before starting any procedure, like formatting or editing the chart. One way to select chart objects is by using your mouse. You can click the individual chart elements to select them. To select the entire chart, click into the “Chart Area.” The Chart Area is the blank area surrounding most of the actual chart elements. For Microsoft Excel spreadsheets. Using Excel.

How to Create and Use Tables with Excel in Microsoft 365 and Office 365

As part of our series of helping customers with their small business needs Longmont Computer Physicians, LLC is offering these free classes on how to use different software programs. Here is our instructional video on using Microsoft Excel spreadsheets. 

Microsoft 365 Beginner class – Excel
Microsoft 365 Intermediate class – Excel
Microsoft 365 Advanced class – Excel

Excel can store information in tables. An Excel table is information saved in a table format and explicitly defined as a table in Excel. When you store information in a table format, you place the different types of information to collect in columns, called “fields” in database terminology. Each “field” contains a separate type of information. Examples could be: “First Name,” “Last Name,” “Title,” “Address,” “City,” “State,” and so on. Each row in the table is called a “record.” A record is a single entry in which you record each type of field information about a single instance of the subject of your table. For example, within a “Customers” table that contains the fields in the previous example, a record in that table might contain the information: “John,” “Doe,” “Mr.,” “111 Nowhere Ln.,” “Anytown,” “MI.” When entering data into a table, avoid creating entirely blank columns or rows! Having entirely blank columns and rows in a table can often lead to problems with sorting and filtering table data. Before you create a table in Excel, consider the information you must collect. Sometimes, it is easier to think of the fields to create after thinking of the subject of the table, first. For example, to create a table to record customer data, you must think about what information you want to collect about your customers.

The types of information you decide to track become the “fields,” or columns, in your table. For the purpose of the example, assume you decided to record your customer’s name, address, city, state, and zip code. When thinking of the table’s field structure, you need to consider how detailed to be with the customer’s information. Poor decisions in the planning phase can be problematic later. For example, do you want to record the customer’s name in one field or more than one field? If you ever want to sort the database by the last name of the customer, you will probably want to store the customer’s name in at least two fields: “firstname” and “lastname.” Noting little things like this during the creation process can save time in editing the table structure later on, after it becomes a problem. After deciding what information to record in which field, enter the titles of these fields as the top row of the table.

The top row in a table is a special row and is often called the “header row.” It is always the top row in a table and it displays the names of the fields for which you are collecting data. After creating the header row, you can then define it as a “table” in Excel to enable the table management features. To do this, select the cells within the header row. Then click the “Table” button in the “Tables” button group on the “Insert” tab of the Ribbon. In the “Create Table” dialog box that appears, the reference to the selected cells appears in the “Where is the data for your table:” field. Check the “My table has headers” checkbox and then click the “OK” button. Doing this then creates the table area within the worksheet and adds a new row into which you can enter your first table record.

Another way to create a table in Excel is to create the header row of your table and then enter as many records as you want to initially record. Then click and drag over the entire table, including the header row and all the table’s records, to select it. After selecting it, click the “Format as Table” button in the “Styles” button group on the “Home” tab of the Ribbon. Then select the table style to apply from the dropdown menu that appears. At this point, the “Format As Table” dialog box then appears. The range of selected cells also appears in the “Where is the data for your table?” field. If your table has a header row at the top of the table, be sure to check the “My table has headers” checkbox. Then click the “OK” button to apply the selected style, and also define the range of cells as a table. Note that each field within the header row of a table has a drop-down button in it. These are “AutoFilters,” which you use to filter data in the table. We will look at using those in a later lesson. Also notice that the table has a different formatting than the rest of the worksheet area in Excel.

New versions of Song Director released in 2018

There have been some new versions of Song Director released in 2018.

To know which version you are using go the pull down help menu and choose “about”

To get the latest version, simply download Song Director again from the website and install it into the same location as before, just like the first time.  The setup program will not overwrite any of your existing data.   Here are the changes:

(more…)

Music Organizing Database software for your song collection

Song Director music player software is the best Alternative to iTunes and Windows Media Player.

Song Director is easier to use with many tasks being done with a click of one button. You can build playlists, sort by artist, song title, genre, year, etc., and easily view all your music.

Free Song Director download

 

Click here for download instructions:

Song Director provides more extensive organizing and search capabilities than iTunes and WMP. It automatically scans your computer looking for music/audio files (MP3, M4a/AAC, iTunes, WMA, WAV, OGG and FLAC files) and builds a searchable database of all your music. I have over 2 TB of music on my PC and it automatically organized all the music quickly. Great for Music Collectors, DJ’s and home users. You can download Song Director right now for free at this link:

Easier to Use than iTunes and Windows Media Player

Song Director is a program that plays and keeps track of all your music files. Song Director is MUCH easier to use and understand than iTunes or Windows Media Player. It unleashes the full power of a modern database system in an extremely simple, easy to use manner.
Automatically enters your information into a database

Song Director automatically scans your computer looking for music/audio files (MP3, M4a/AAC, itunes, WMA, WAV, OGG and FLAC files) and builds a searchable database of all your music. Once this database is built, you can quickly and easily play songs, build playlists, sort by artist, song title, genre, year, etc., and easily view all your music.
Easily find song, artist or other criteria

Find any song or artist in the database by simply typing the first few characters of the song or artist name. As you type, Song Director immediately zeroes in on the record you want.

Song Director lets you specify any criteria to further limit the records displayed or printed. You can then play, view, edit, or export just the songs you selected. You can search for any string of characters in all your song records.
Create Categories for your music

You can easily select one or multiple records to display or play. You only have to deal with the songs you want, not your entire collection. A unique feature allows you to organize songs into any Category you wish, then view these Categories separately or combined.
Song Director – Main Grid screen: If you are a Music Collector or DJ looking for easy to use free music, Disc Jockey software, Song Director is great for a DJ wanting to use Windows for customized play lists and song searches. A Disc Jockey can sort on 23 fields including beats per minute, bit rate, etc. And can have endless customized searches to find the perfect song.

http://songdirector.com/

 

Song Director Press Release

Public Contact:
Computer Physicians, LLC
Website: http://songdirector.com/

FOR IMMEDIATE RELEASE

NEW FREE SOFTWARE PROVIDES EASIER-TO-USE ALTERNATIVE TO iTUNES AND WINDOWS MEDIA PLAYER

LONGMONT, Colo, – April 4, 2016 – Song Director is a new music organizer and player offered as a free and easier-to-use alternative or complement to iTunes and Windows Media Player.  This new program provides more extensive organizing and search capabilities than either of those programs.

“Simplicity and ease-of-use has been the dominant goal during every phase of development and testing. I’ve seen too many people confused and frustrated with iTunes and WMP, and wanted to do something about it”, says the software author, who has been developing music software for the last 20 years.

Song Director automatically scans your computer for audio files and builds a searchable database of all your music. Once this database is built, you unleash the full power of a modern database system to search, play, and organize your music. Quickly build playlists, export to other devices, or sort and search your music in countless ways. Searches for songs or artists are quick by simply typing the first few letters of a song title or artist. You can then play that song or add to a playlist with one click.  Alternately, create advanced searches based on any criteria, going far beyond the search capabilities of iTunes and Windows Media Player.

Song Director by Computer Physicians, LLC is available by free download at: http://songdirector.com/ and runs on all modern versions of Windows from Windows XP to Windows 10.