Formulas & Functions Archives

Excel Date Calculations Part 2: Setting Sequential Dates

December 21, 2016 by Tyrone Pernsley

Excel offers a variety of ways to perform date calculations. In part 1 of this series, we showed you how to find the difference between dates. In this week’s post, we cover setting sequential dates in Excel.

Sheryl is getting ready to perform her annual budget. Her job pays her every two weeks. As a result, 10 months of the year, she receives two paychecks, but there are two months where she ends up with three paychecks. She wants to find each payday in the upcoming year and highlight the months where she will receive three checks.

She starts off with her current payday date:

To find the two week pay schedule dates she will:

Enter the formula =A2+14
This yields the next pay day
Then copy the formula across to find all dates
Then look for the date with three in a month and highlight them

In the example, we see that Excel automatically finds the date 14 days from the original date input. In copying the formula across it populates each date two weeks apart.

We at Learn Excel Now hope you now feel confident setting sequential dates in Excel. This process can be used for a variety of business and personal situations.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Rank Function: What is It & How to Use It

November 30, 2016 by Tyrone Pernsley

One of Excel’s advanced functions is the RANK function. This formula is used to rank numbers in a dataset by either ascending or descending order.

Let’s say you were running a customer loyalty program based on earning points. You need to rank customers’ point totals to determine what rewards they receive. You could do a simple sort, but your boss wants the customer names to stay in the same order.

This is the example we will use:

You will use the RANK function for this challenge, but let’s first explore how the function works.

Syntax of the Rank Function

=RANK(Number, Ref,[Order])

When you use the rank function, your first argument is the number. This is where you specify the cell containing the number you want to be ranked. Next, you identify the range of numbers to be used as the reference (Ref). Finally, you select the order, which is ascending or descending.

Solving the Challenge

So, we will identify B2 as the cell to be used. Then, identify the range as B2 through B11:

Then, select Ascending order and we get our first result:

Before moving onto the remaining lines in the table, we need to add absolute referencing to the range so that it stays consistent while the number being compared changes for each line. A reminder to use the $ between the letter and number of the cell for absolute referencing:

Once you’ve added the absolute reference, you can now copy and paste the formula all the way down to reveal the rank for all lines:

We at Learn Excel Now hope you feel comfortable using the RANK function after this article.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

How to Use the Excel MEDIAN Function

November 9, 2016 by Tyrone Pernsley

In this week’s Excel tip, we cover how to use the Excel MEDIAN function. The MEDIAN function is used the return the median value within the defined range.

For this demonstration, we will use the follow example:

The task assigned for this spreadsheet was to find the median sale amount. To do this, we will use the MEDIAN formula:

=MEDIAN(Number1, [number2]..)

Since the goal is to find the median in a single range, the formula becomes:

=MEDIAN(D2:D21)

And once you enter the formula, you get the return value:

Since there were 20 cells within the range, the formula found the average between two middle values.

We at Learn Excel Now hope you feel comfortable using the MEDIAN function now.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Formulas: How to Use the LEFT and RIGHT Functions

November 2, 2016 by Tyrone Pernsley

This demonstration covers how to use the LEFT and Right Functions in Excel. These are text functions. In the LEFT function, you can pull a set number of characters out of a cell into another cell starting at the leftmost point. The RIGHT function performs the same except starting at the rightmost point.

Both functions are related to the MID function, which we covered back in September. At the time, one of our customers asked for a demo on the MID function. Since then, we’ve had several follow up requests on the LEFT and RIGHT.

For today’s demonstration, we will be using the following spreadsheet:

In this example, Column A contains a 6-digit number hyphenated with a 4-digit number. The 6-digit number is the order number and the 4 digit is the client ID. Your job is to separate the order number and client ID into Columns C and E, respectively.

How is this done?

For the order number, we will use the LEFT function. We know the code is 6 digits, so we have the number of characters. So in cell C2, you will enter the following formula:

=LEFT(A2,6)

For the client ID, we will use the RIGHT function. In cell E2, you will enter the following formula:

=RIGHT(A2,4)

LEFT Function Syntax:

=LEFT(Destination Cell, Number of Characters)

This tells Excel: Starting on the left of this specified cell, copy to this many characters.

RIGHT Function Syntax:

=RIGHT(Destination Cell, Number of Characters)

This tells Excel: Starting on the right of the specified cell, copy to this many characters.

As you can see in the above demonstration, once the functions have been entered, they can be copied all the way down to finish filling in the data.

We at Learn Excel Now hope you feel comfortable using the RIGHT and LEFT functions.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter.

Kevin – Learn Excel Now

How to Show Formulas in Excel

October 26, 2016 by Tyrone Pernsley

In this week’s blog post, we cover how to show formulas in Excel. This convenient feature is ideal for identifying which cells contain formulas and what those formulas are.

John was recently hired in human resources for a large sales firm. His job is to manage the salaries for the sales, accounting and shipping departments. Once hired, he received the following spreadsheet from his predecessor.

He was not a fan of the layout and functionality of the spreadsheet. He wanted to change it around to make more sense to him and to give him more control over the data. However, he knew some of the cells contained formulas and moving things around could throw off the whole spreadsheet.

So, his first step in taking ownership over the spreadsheet was to identify which cells are using formulas. There are two ways to show formulas in Excel:

On the Excel Ribbon, go to the Formulas tab and click on the Show Formulas button
Keyboard shortcut: Ctrl+` (Control and the accent mark, aka Tilde)

The following Gif provides a demo using the first option:

However, using the Ctrl+` would result in the same.

We at Learn Excel Now hope you now feel confident finding the formulas in your spreadsheet.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Formulas: Using Trace Dependents to Find Data Solutions

October 19, 2016 by Tyrone Pernsley

In last week’s post, we covered how to use Trace Precedents to find and resolve formula errors. In this week’s follow up, we cover how to use trace dependents to see which formulas use the data contained in a particular cell.

Donna was recently sent her company’s sales spreadsheet:

The commission rate was set to 5% for sales over $500. Her boss wants to increase the rate to 6%. She has two tasks: identify which sales will be affected by the increase and how many sales are over $500.

There are a number of ways Donna can find the data she needs. She could manually count the sales columns. She could use a lookup function. However, she prefers to have a clear visual to show which ones are the important ones.

By using Trace Dependents, she can quickly see which bonuses are for sales over $500, allowing her to determine which ones will be impacted and how many there are. Here is how she used Trace Dependents:

Find the cell with the relevant data – in this case, the 5% in the commission table (Cell I6)
While you are on that cell, click the Formulas tab in the Ribbon
Under the Formula Auditing menu, click on Trace Dependents
The arrows will point to every cell using a formula which uses I6

We at Learn Excel Now hope you feel confident using Trace Dependents now. There are many times and reasons this tool will come in handy.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Formulas: Using Trace Precedents to Find & Resolve Errors

October 12, 2016 by Tyrone Pernsley

The power of Excel comes from the ability to do complicated calculations using formulas and functions. Sometimes, though, there are errors in formulas that give an error message or return the wrong value. In those instances, Excel also provides tools to resolve errors and check your work. One such tool is trace precedents.

The trace precedents tool finds any and all cells that provide information for the target cell. For example, if you summed up the totals in A1 and B1 into cell C1, both cells A1 and B1 would be precedents for C1.

Some errors in Excel are related to one or more precedent cells giving information that throws off the formula. Trace precedents is a great way to determine if there is a cell causing the issue, and if so, which cell needs to be corrected

For an example of using trace precedents to resolve formula errors, we will use the following spreadsheet:

In this instance, the table is using a simple formula to create revenue projections. The previous year’s quarterly total is increased by the projected growth of 5%. As you can see though, the Q3 project, cell I5, is showing an error message.

So, we will use the trace precedents tool to see if there are any cells causing the issue:

In the demo above, the error was created because one of the cells was incorrect. Instead of the cell containing the percentage amount (5% in cell F4) it was trying to pull the definition of that number one cell above (F3).

The issue could have been caused by simple human error. The person entering the formulas could have simply clicked on the wrong cell.

Once the wrong cell was identified using trace precedents, it was a simple matter of editing the formula to the correct cell and the error was resolved.

There are many instances where this tool could come in handy. Aside from resolving errors, sometimes you might just want to see which cells feed into a formula.

Stay tuned for part 2 next week on Trace Dependents!

We here at Learn Excel Now hope you now feel confident using the trace precedents tool to resolve formula issues.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel MAX and MIN Functions: Essential Tips for Success

September 21, 2016 by Tyrone Pernsley

There are far too many Excel functions for one person to learn, but there are essential functions every user should know. The MAX and MIN functions are two such functions. The MAX function allows you to find the highest number in given range. The MIN function does the opposite, providing you with the lowest number in a defined range. This quick tutorial will cover the basics on how to apply both functions.

For this brief tutorial, we will use the following dataset:

Let’s say you had this sales table and your manager wanted you to find the highest sale and lowest sale over a given time period. Using the example above, it might actually be easy to eye it up because there are only 20 rows of data. But in a real sales spreadsheet, it could range hundreds or thousands of rows.

So, we will use the MAX function. The syntax is easy:

=MAX(Number 1, number 2)

In above table, we are looking for the highest sale in column D, which means the function will look like:

=MAX(D2:D21)

Once the formula is entered, it will return the desired value:

On the flip side, the MIN function has a similar syntax:

=MIN(number 1, number2)

And again, our table is in column D, so the function becomes:

=MIN(D2:D21)

Once the formula is entered, Excel will return the desired value:

In addition to SUM, IF statements and VLOOKUP, the MAX and MIN functions are the most essential functions in Excel. We at Learn Excel Now hope you now feel confident in applying these functions to your everyday Excel use.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Functions: What is the MID Function & How to Use It

September 14, 2016 by Tyrone Pernsley

Excel offers a variety of different formulas and functions for everyday use. This week, we will focus on the MID function. This function allows you to isolate a sub-string of data within a larger string in a new cell. The syntax is simple and straightforward, making it an ideal function for quick tasks.

There are many reasons and ways you will use the MID function. Today’s example comes directly from a Learn Excel Now fan who emailed us with an issue he was having.

Brian wrote:

“The most boring and time-consuming task I have at work is to pull the processing numbers out of the sales database to send to our warehouse team. The sales report exports into Excel and all of the order info is jammed together on one line. I have to go line by line and rewrite the individual processing number. Depending on our daily orders, it can take me several hours to finish. And with so many lines to go through, it’s really easy to miss a number or get the line wrong. My boss has talked to me about it. Can you help?”

Yes, Brian, we are here to help. Let’s explore how the MID function can be used to help your task.

The syntax for the MID function is:

=MID(Text,Start_number,Number of Characters)

Simply put, this is saying: pull the data out of this range (text), starting on character (Start_Number) going this many characters after it (Number of Characters).

This spreadsheet will be used to approximate Brian’s report:

As you can see, the Processing Number starts on character 13 and is 5 characters long. Thus, the way we would write this formula is:

=MID(A3,13,5)

Now the processing number is nice and clear on its own line:

IMPORTANT: In this example, each line has same the number of characters. So, you can copy and paste all the way down, however, for lines with different numbers of characters, you will need to count to the starting and end points individually.

For Brian, using the MID function will save a ton of time and ensure accuracy.

We at Learn Excel Now hope you found this week’s tip on the MID function useful. There are many times this will come in handy, you just have to recognize the opportunity!

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter.

Kevin – Learn Excel Now

Excel IF Statements Part 3 – Nested IF Statements

August 10, 2016 by Tyrone Pernsley

In this week’s IF statement series, we are covering Nested IF Statements. This convenient formula allows you to return values based on multiple logic tests built into – or nested – in the formula itself. We will explore several uses of nested IF statements in demonstrations below.

Syntax of Nested IF Statements

When you write a nested IF statement, you are essentially saying “If true, do this, if true, do this, if true, do this…if false, then do you this.” You can nest up to seven IF criteria within the formula.

The syntax for nested IF Statements is:

=IF(True-This,IF(True, this,IF(True,this,IF(True,This,IF(False,this))))

Notice: For each new IF statement, you open a new parenthesis, however, the parenthesis closing all come at the end of the formula.

Uses for Nested IF Statements

There are many reasons to use nested IF statements and you will have to determine the best situations to employ them. For the purposes of demonstration, we will use nested IF statements to calculate commission rates based on a sliding scale and salary increases based on years of service.

Calculating Commission on a Sliding Scale

If you remember our Simple IF Statements from last week, we used a single IF condition to return a flat bonus rate or determine No Bonus. But, what if there is a sliding scale? By nesting several IF statements together, you can create a dynamic formula that returns the commission amount based on a sliding scale.

Let’s say you had the following spreadsheet:

You can see the sliding scale in the table to the right. To write this scale into a nested IF statement you would write it as:

=IF(D2<101,”No Bonus”,IF(D2<201,D2*0.03,IF(D2<301,D2*0.03,IF(D2<=500,D2*0.04,IF(D2>501,D2*0.05)))))

Here, what you are saying is: IF the amount in column D is less than 101, return the value No Bonus, IF it’s less than 201, times the amount by 2% (0.02), IF less than 301, times it by 3%, IF less than or equal to 500, times it by 4%, and if it’s greater than 501, times it by 5%.

Here is how that looks in Excel:

As you can see, it automatically calculates the commission payout based on the range of the sale amount.

Calculating Salary Increase Based on Years Employed

Here we have a similar issue. We can see the employee list, their current salary and years employed. We have a table that shows what percentage their salary should increase based on length of employment.

To quickly find the new salary amount, you would write the following IF statement:

=IF(C2<6,B2*1.02,IF(C2<11,B2*1.03,IF(C2<16,B2*1.04,IF(C2<21,B2*1.05))))

Here, we are saying “IF the years in column C are less than 6, then increase the salary in column B by 2% (1.02). IF it’s less than 11 years, increase by 3%, IF less than 16 years, increase by 4% and IF greater than 21 years, increase by 5%. “

Here is how it looks in Excel:

As you can see, there are a variety of ways and reasons to use nested IF statements. We here at Learn Excel Now hope you know feel comfortable applying these functions in your spreadsheets.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Date Calculations Part 2: Setting Sequential Dates

Excel Rank Function: What is It & How to Use It

How to Use the Excel MEDIAN Function

Excel Formulas: How to Use the LEFT and RIGHT Functions

How to Show Formulas in Excel

Excel Formulas: Using Trace Dependents to Find Data Solutions

Excel Formulas: Using Trace Precedents to Find & Resolve Errors

Excel MAX and MIN Functions: Essential Tips for Success

Excel Functions: What is the MID Function & How to Use It

Excel IF Statements Part 3 – Nested IF Statements

Sitemap

Connect with Us

Contact