VirtuallyUseful – Random Tech Babble

October 19, 2017

Blogtober Post 1 – Recommended functions to start learning Excel with

Filed under: Excel — Ray Olander @ 6:52 PM

With all the possible things to learn in Excel, people often wonder where to even start. I have been using Excel for a very long time now, and the following are the functions I use to do probably a good 90% of all my work. If you can learn these, you’ll be amazed at how many things you’ll be able to use Excel for. I wouldn’t say learning these will SAVE your job, but they can most definitely get you noticed.

This is a list of the functions that I use on a daily basis:

String operations: LEFT, RIGHT, MID, LEN, TRIM, FIND, VLOOKUP, and CONCATENATE (the short version I prefer is simply the & symbol)

Logical operations: IF, NOT, ISERROR, AND, OR

Sum/count operations: SUM, COUNTA, COUNTIF

Here are examples and explanations for each of the functions:

LEFT(cell, number of characters to select from the beginning of the line)

Returns a specified number of characters from the left-most character in a string

RIGHT(cell, number of characters to select from the end of the line)

Returns a specified number of characters from the right-most character in a string

MID(cell, where in the string to start, how many characters to select)

Returns a specified number of characters from a given starting position within a string

LEN(cell you want to find the length of the text in)

Returns the length of a string

Example:

TRIM(the output of one of the above functions)

Removes white spaces from the beginning and end of a string

Example:

FIND(What text to find, in what cell to look, starting at what position)

Return the position value of a character or string found within another string

Example:

VLOOKUP(what test to find, where to look for it, offset from located value to read, return exact (false) or similar (true) results)

Return a separate value in a table offset by a specified amount in the same row as the index value found within the table

Example:

CONCATENATE(text1,text2,…) (or & & etc… which I’ll show later)

Combine two or more values

Example:

IF(condition(s) to evaluate, what to do if true, what to do is false)

Take different actions based on the result of one or more conditional statements

Example:

NOT(whatever is being evaluated)

Invert the results

Example:

ISERROR(whatever is being evaluated)

Returns a true or false based on a wide array of possible errors you might receive

Example:

AND(condition1,condition2,…)

All conditions evaluated must be true in order for the outcome to be true

OR(condition1,condition2,…)

Any condition being true causes the outcome to be true

Example:

SUM(number/range1,number/range2,…)

Adding up numbers to get a value

COUNTA(number/range1,number/range2,…)

Check how many cells in a range are not empty

Opposite of countblank

COUNTIF(range, criteria)

Checks how many cells match one given criteria

Countifs is the formula to use to evaluate multiple criteria

Example:

 

Stay tuned for Blogtober post 2 where I apply a number of the above commands to the same syslog entry use in the previous examples to parse out the firewall name,

and to show how to breakdown that formula, or any formula, to better understand what it is doing.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: