Analysis without programs using Microsoft Excel

Workflow, business operations, COVID, Work from home

Published: 2/5/2021

Excel Formulas Needed To Analyze Data

So, you to analyze data BUT you’re not a computer programmer. Well, all is not lost and you may not have to bug the IT department or hire a programmer to extract meaningful information from datasets. All you need is a subscription to Microsoft Office or LibreOffice and this cool formula below:

The IF Formula

The IF Formula:

Conditional statements (If, then, else) are an integral part of any program. In Excel, they function much in the same way they do in computer code.

You specify a condition, then tell the computer or spreadsheet what to do if that condition is met. You’ll also specify what to do if that condition is not met.

Syntax of the formula

Here’s how to start.

First, you type the ‘=’ sign into the Excel formula bar. Excel requires that you start all formulas with this symbol.

Next, type the word ‘IF’ followed by the condition you want to check for and what you want done if the condition is or is not met (all of this must be encapsulated in parenthesis).

=IF(A1=”Super Easy CRM”,”Buy a Subcription”,”Not worth it”)

In the above example I am telling Excel to search the contents of cell A1 for the string (series of characters) Super Easy CRM.

Then I’m saying "Ok Excel, when you find it I want you to output the string Buy a Subscription". Now if Excel does not find the string “Super Easy CRM”, I am demanding that it outputs the string "Not worth it".

Example of the IF formula in action

As you get more comfortable with the formula, you can embed more formulas that will trigger when conditions are met or not.

For example, you can tell Excel to calculate sales tax based on the state indicated in the cell data.

So let’s pretend you have customers in Florida and in Texas. Let’s also imagine the sales tax is Florida is 6% and the sales tax in Texas is 10%. We’ll also assume that there are no other states listed in your spreadsheet.

In the ‘A’ column, you’ll find your states and in the ‘B’ column you’ll find the dollar amounts they are charged.

You need to ensure your customers are being charged the appropriate sales tax so you devise the formula below:

=IF(A1="FL",(1.06*B1),(1.1*B1))

This looks a little different than the first example but the logic is the same.

Here Excel is going to look at the contents of cell A1 and if it sees that the state is FL then it will multiply the dollar amounts charged by 1.06 (FL sales tax). However, if it does not see FL listed it will multiply the value by 1.1 (TX sales tax).

This cool formula can be extended upon and customized to suit your individual needs. The only real limit here is your imagination.

By following the above tips, you’ll be analyzing data like a computer programmer in no time!