Microsoft Excel: Useful Functions You Should Get to Know

In this article we’ll discuss categories of functions – what they do and various examples – and to illustrate how they work, we’ll show you several example spreadsheets. We encourage you to follow along by creating your own spreadsheets.

There are many other functions, of course, and by no means are these the only ones that you should learn. We’re just giving you a set of really useful functions that you’ll need to use on a regular basis. The next lesson will demonstrate some more advanced functions that you might use, but a little less often.

Math and Financial Functions

Again, this is not a complete list of math or financial functions, but an example of a few that you should get to know. We recommend testing them in a spreadsheet of your own.

Functions	Purpose
SQRT	Square root, e.g., SQRT(4)=2 because 2*2=4
PMT	Loan payment
DEGREES	Used by engineers, for example, to convert degrees (e.g., 360⁰) to radians (e.g. 2π)
GCD	Finds the greatest-common divisor between two numbers. For example, GDC(5,15)=5, because 5 is the largest number that divides 15. 3 also divides 15, but that is not the largest divisor they have in common.
RAND(23)	Generate a random decimal number between or equal to 0 and 1. You could use this for example, to pick a contest winner (see example below). RANDBETWEEN is easier to use, since you do not have convert this decimal number to a whole number, e.g., 3 is easier to use than 0.3.

Also read Top 4 Most Important Financial Excel Formulas Explained

 

Example: Random Contest Winner

Here is an example of how you could use RANDBETWEEN(). Suppose we have eight employees in the office and the boss is giving away an iPad via a random drawing.

Don’t use a hat and pieces of paper to pick a winner, use high technology such as Excel!

Make a spreadsheet such as the one below:

We use =RANDBETWEEN(1,8) in cell B10 to generate a whole number between 1 and 8.

Each employee’s name is in the range A2:A9 since there are eight employees, so we need to pick a random number between or equal to 1 and 8. The person whose number comes up wins the iPad.

You know the employee’s position in the list – James is 1, Mark is 2, etc. Use the INDEX function to get the employee’s name to assign the randomly generated number to the corresponding name. This is how we thus pick our winner.

• A2:A9 is the range.
• B10 is the number of the employee who won.

In this example, 1 means pick the first column in the range and =INDEX() is a function that retrieves the value of a cell (Jam.es =1 so he is our winner).

If you want to pick another name – such as if the winner must be present to win – then all you need to do is change the spreadsheet. You can try it yourself, simply change the width of a column or add data. Anytime the spreadsheet updates, it regenerates a new random number and picks a new winner.

Logical Functions

Logical functions are used to test whether something is true or false.

Function	Purpose
IF	If(<test>,X,Y)If <test> is true, then the value is X, otherwise the value is Y.For example this formula =IF(C2=5,”C2=5″,”C2<>5″)Displays “C2=5” if C2=5, otherwise it will display “C2<>5”
AND	=AND(C3=5,C4=5)Displays “TRUE” if both C3=5 and C4=5
FALSE	Sets the cell to false
TRUE	Sets the cell to true

Also read Essential Excel functions: Mighty IF logic

Example: The Decision Maker

Here is an example that demonstrates using logic functions.

Jane is trying to decide what to wear to high school. Jane likes both John and Jim. If either one of them dumps their girlfriend, then Jane will put on her sexy clothes and try to reel one of them in, otherwise, she only cares if it is raining or sunny.

Jane happens to be an Excel pro, so she decides to put her decision-making process into a spreadsheet.

Let’s outline our logic. Whether or not John has a girlfriend is a completely random event – either he does (1) or he does not (0). This odd looking formula =1=RANDBETWEEN(0, 1) is really two.

Excel generates the random number 0 or 1 using RANDBETWEEN(0, 1). Excel tests whether this number is 1 and if so, the cell will be “TRUE” meaning John has a girlfriend.

We simply apply the same formula to cell B7, which determine whether Jim has a girlfriend.

Remember that Jane will make her move if either one of these guys is available. The formula =AND(B6,B7) returns TRUE if both John and Jim have a girlfriends.

Now, Jane wants to check the weather to see if it is raining or not. If the random number is 1 (TRUE), it is raining. We use the same formula RANDBETWEEN(0, 1), such as we used in cells B6 and B7 for Jim and John’s relationship status.

Now determine what to wear. We use =IF(NOT(B8),A3,IF(B9,A2,A4)) to determine, which outfit Jane will wear.

NOT(B8) is the same as saying B8=FALSE.

Writing any cell name inside an IF statement is the same as saying it is true. For example =IF(B8,”is true”, “is false”) is equivalent to =IF(B8=TRUE,”is true”, “is false”). So, IF NOT(B8)=TRUE, i.e., B8=FALSE, then that means one of them does not have a girlfriend. In that case Excel selects cell A3 (sexy outfit).

If NOT(B8), i.e. B8=TRUE (meaning both John and Jim have girlfriends), then we check if it is raining or sunny. If B9 is TRUE then it is raining and Excel selects A2 (“rainy day outfit”).

If B9 is FALSE then it is not raining and Excel will select A4 (“nice weather outfit”). In this case, both John and Jim have a girlfriend and it is not raining and Jane will wear her nice weather outfit.

Date and Time Functions

Dates are stored in excel as numbers, which means you can do math with them. You can use +1 to calculate the next day and you can subtract one date from another to find how many days have elapsed between two dates.

Formula	Result	Purpose
=NOW()	9/16/2013	Get the current date and time
=DATEVALUE(“9/16/2013″)	41533	Convert date in text format to number. This number is called the “serial number”. Number 1 is January 1, 1900. 41,533 is 41,532 days after that or 9/16/2013.
=WEEKDAY(NOW())	2	Extracts the date as a number. 1 is Sunday and 7 is Saturday.
=NOW()+1	9/17/2013	Since dates in Excel are stored as numbers, you can do math with them.

For example, Excel cannot simply add =9/16/2013 + 1. It must first convert it to a number that makes sense to it. In the screenshot, you see we first had to assign the DATEVALUE() function to the formula so Excel knows the text’s “value” is a “date.”

 

Example: When to Take Vacation

Here is an example that demonstrates how to use various date functions.

The boss says you can take vacation based upon your sales. If you make $1 million in sales you get two weeks (10 working days). If you make $10 million in sales you get three weeks (15 working days). Make less than $1 million in sales and you get a new job elsewhere.

You are a great salesman, so you’re planning to make at least $10 million in sales and do it quickly. You estimate it will take you fifty days to meet your quota. You want to project when you can go on vacation, so you make a spreadsheet.

To find the vacation date, you cannot simply add 50 days to the first of the year. It has to be 50 working days – not including holidays. Further, since no one wants to go on holiday in the middle of the week, you have to find the following Saturday, unless the date calculated already is a Saturday.

How do you do this calculation? Let’s look closely at the spreadsheet below:

If you project $15 million in sales, then enter that value in cell B2. You also estimate it will take 50 work days to reach that goal, which you enter into B3.

From here, we can calculate what date it will be 50 work days from January 1, and then display the following Saturday’s date – your first day of vacation!

The formula =WORKDAY(B1,B3,A13:A20) adds B3 (50 work days) to B1 (January 1) excluding the holidays listed in the range A13:A20.

Next, you must find the next Saturday. This is a bit tricky. The whole formula is =IF(WEEKDAY(B4)=7,7,7-WEEKDAY(B4))+B4.

To explain, remember that Friday and Saturday are the 6^th and 7^th week days, respectively. March 14, which is our projected first day we’ll be eligible to go on vacation is March 14 (cell B4), which is a Friday (6).

We then take the number 7 (Saturday) and subtract the value from B4.

Once Excel determines how many days’ difference there is between your projected vacation day and the following Saturday, it adds that difference, in this case 1, to the value in B4, and displays it in B5.

Once you determine when to go on vacation, you can figure out when to come back. If your sales are greater than or equal to $1,000,000 then you have 10 vacation days. If they are greater than or equal to $10,000,000, you get 15 vacation days.

The formula we’re going to use to determine our return date is =IF(B2>=A10,WORKDAY(B11,B5),WORKDAY(B11,B4)).

Here we first determine the value of B2 (sales made) and then compare it to the value in A10 ($10,000,000).

If the value is greater than or equal to $10,000,000, then the formula will add 15 days to cell B5 and print the result in B6, which is your return date.

Otherwise, it will add the value in B9 (10 days) and print that in B6.

We don’t care about holidays because we will be on vacation, so we do not include them in the WORKDAY() function.

Sadly, as useful as this is, Excel still cannot tell you whether you will even want to return to work once your vacation ends!

Also read Excel Formulas: 10 Formulas That Helped Me Keep My Job

---

Courtesy: howtogeek.com