Useful Microsoft formulas for the Discerning Analyst

 

Spreadsheets has always been the bread and butter of the Analyst. Not only it gives you a quick and compact platform to analyze and share your analysis throughout the organization it also provides some flexibility regarding reporting.

On this tutorial we will give you a brief walk-through and quick guide regarding basic and most used formulas in Microsoft Excel.

(01.) “IF condition” – The if condition in programming is a way to execute a code to see if a condition is true and if the condition was false execute another code.

Below is the formula in Microsoft Excel please take note of the items highlighted in red.

=IF(LOGICAL STATEMENT , IF CONDITION WAS TRUE , IF CONDITION WAS FALSE)

Lets use the code to compare on excel spreadsheet cells A2 and C2. We need to see if both columns contains the same sentences.

Formula:

=IF(A2=C2,"SENTENCE THE SAME","NOT THE SAME")

Outcome:

Now lets do the same formula but replace the cells to A2 and C3 for cells A3 and C3.

Formula:

=IF(A3=C3,"SENTENCE THE SAME","NOT THE SAME")

Outcome:

 

(02.) “IsNumber()” – Checks if a cell contains a numeric value, returns “TRUE” if numeric and “FALSE” if not.

Below is the formula in Microsoft Excel please take note of the items highlighted in red.

=ISNUMBER( VALUE TO CHECK )

Now let us use the formula in Excel to see if the sentence in cell A4 is number.

Formula:

=ISNUMBER(A4)

Outcome:

Lets try to change the value of cell A4 to numeric.

(03.) “SEARCH()” – Returns the number of character on when the searched text in a cell is found.

Below is the formula in Microsoft Excel please take note of the items highlighted in red.

=SEARCH("THE TEXT TO FIND", "THE CELL", "START NUMBER")

Now let us use the formula in Excel to SEARCH in cell A5  to find the word “THOUGHT”.

Formula:

=SEARCH("THOUGHT",A5)

Outcome:

 

(04.) “CONCATENATE()” – Joins two cells into one cell.

Below is the formula in Microsoft Excel please take note of the items highlighted in red.

=CONCATENATE("CELL TO JOIN","CELL TO JOIN")

Now lets try to join cells A6 and A7 together

Formula:

=CONCATENATE(A6,A7)

Outcome:

 

Now lets try a “LIKE” expression in SQL Database Query we use LIKE to get an information out of a specific column which contain some specific TEXT,NUMBER or anything you need to search that specific column. On Excel we don’t have “LIKE” statement.

on PL SQL Syntax

LIKE '% SOME TEXT %'

But we can emulate “LIKE” by combining three EXCEL functions all together. On this exercise we will use IF , SEARCH and ISNUMBER.

Below is the formula in Microsoft Excel please take note of the items highlighted in red.

=IF(ISNUMBER(SEARCH("THE TEXT TO FIND",THE CELL)), "IF THE CHARACTERS EXIST", "IF THE CHARACTERS DON'T EXIST")

On this exercise we are going to look for the word “IRRITATED” on cell A7 and return the value TRUE if exist and False if not.

Formula:

=IF(ISNUMBER(SEARCH("IRRITATED",A7)), "TRUE", "FALSE")

Outcome:

 

We have covered some basic Excel formulas, That’s all for now if you want to have a sample file of the exercise A_KOAN (A Koan).

If you like this tutorial please like and share this exercise on the button below. If you have any comments or suggestion please feel free to comment on the comments section below. Please message us on facebook so we can create another tutorial for you. Thanks – Data Jackal

2 thoughts on “Useful Microsoft formulas for the Discerning Analyst

Comments are closed.