formulas and functions

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 Date Calculations Part 1: Finding the Difference Between Two Dates

December 14, 2016 by Tyrone Pernsley

Excel has several built-in date functions you can use to quickly find important information. These are known as Excel date calculations. Today, we will focus on how to find the difference, in number of days, between two different dates. We will also cover how to find a date based on number of days from a given date.

For today’s example, we will use the following spreadsheet outlining due dates for a library:

Here, we have the due date and the current date. To enter the current date into your spreadsheet, you will use the formula =TODAY(). It will autofill with today’s date.

What we don’t have is the number of days overdue, and the overdue fee. To find that, we will:

Subtract the due date from the current date

Multiply the number of days by the fee per day

As you can see, Excel makes it easy to find the exact number of days and then calculate the amount owed. This process involves first finding the number of days and then separately finding the amount due. But you can also build the date calculation right into the fee calculation.

You will use the parenthesis to indicate which function you want performed first (always remember: Excel operates on the Order of Operations).

So, the first part of the calculation will be: =(B3-A3)

Then, tell the formula to also multiply by the amount owed column: =(B3-A3)*D3

Finding a Date Based on Number of Days

Let’s say you had the same spreadsheet, except that the original due date was missing, but you still have the number of days. Your job is to find the original due date. This is basically a reverse of finding the number of days overdue. You will enter:

=B3-C3

This is how you use Excel to find the number of days between two dates or vice versa. Next week, we will cover additional date functions you will want to know. We hope you found today’s lesson on Excel date calculations useful.

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 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 IF Statements Part 2 – Intro to Simple IF Function

August 3, 2016 by Tyrone Pernsley

In Part 2 (get Part 1 Here) of our IF Statement series, we are discussing simple IF statements. These are the basic formulas for returning a values based on your (the user’s) criteria. These functions can be used for a variety of purposes and the examples below explore some of the potential uses of IF statements.

A Definition of IF Statements

IF Statements are logical functions in Excel. When you define the criteria for the statement, you are essentially saying, IF the defined condition is true, then return this value, otherwise (if false) return this value.

The syntax of IF Statements is:

=IF(logical_test, [value_if_true],[value_if_false])

Uses of IF Statements

As you use Excel, you might come across various instances where IF statements will be useful. Below are two examples based on real-world issues where these functions come in handy.

Example 1: Making Purchase Decision Based on Margins

Let’s say you received the following spreadsheet:

Your boss is trying to determine if the company should purchase wholesale products from the new vendor. You have your wholesale price the company will pay and the expected retail price the company can charge. Your boss wants you to find the net gain and profit margin. For any product where the profit margin is 30% or greater, you need mark it “Purchase.” For any product with a profit margin less than 30%, you need to mark it “No”

So, here is your step-by-step process:

Subtract the Wholesale amount from the Retail amount under the Net column
Divide the Whole amount by the Net amount in the Margin column – change number to a percentage
Highlight both cells and use the fill handle to drag the Net amount and Margin percentage all the way down.

Now, at this point, you could manually enter Purchase or No by eyeing up the margin percentage to see if it’s greater or equal to 30%. But you can save time and ensure accuracy by using an IF Statement.

Here is how you would do that:

Under the Purchas column, enter your formula: =IF(E2>=30%, “Purchase”, “No”)

What this formula states is “If the value in column E is greater than (>) or equal to (=) 30%, then return the value Purchase. If not, return the value NO.

Once the formula is entered, you can again use the fill handle to drag it all the way down.

Example 2: Determining Commission Bonus Payout

You received the following spreadsheet:

Your job is find the commission rate of 5%. For any sale over $300, the salesperson receives the 5% sales commission bonus. Otherwise, they don’t get a bonus.

Step-by-step:

In the Percent of Sale column (E), enter the following formula: =d2*0.5
This will yield the 5% sales amount
Copy the formula down using the fill handle
Under the Commission Payout column (F), enter your IF Statement: =IF(D2>300, “E2”, “No Bonus”)

What this formula states is: If the sale amount in column D is greater than 300, then return the commission value in column E, else, state No Bonus

As you can see, there are a variety of uses for IF statements. It really depends on what you need to use Excel for. Coming next week: Nested IF Statements!

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 Calculate a Subtotal In Excel Using the Filter

July 20, 2016 by Tyrone Pernsley

Excel has many ways to calculate data. If you know the right formulas and functions, you can find out just about anything you want to know about your data. Today’s lesson is on using the subtotal formula to find various totals based on the filter option in Excel.

Imagine you are running sales numbers using the following the spreadsheet:

If you remember from last week’s blog post on Sorting and Filtering data, we have gone ahead and added the filter. Now, if you wanted to find the sales total, you can use the following formula:

=sum(range)

This is the standard way to find a total. But, as you can see, once you use this formula and change the anything from the filtered drop-down menu, the sales total doesn’t change with it:

So, how do you get the total to change with changes you make on the filter? This is where you will use the Subtotal formula. The Subtotal formula is:

=subtotal(function_number, ref1…)

Note: You need to select which function you want the subtotal to use. For finding sums, we use function 9-Sum. As you can see from the following menu, there are multiple functions to choose from:

Once the function is selected it’s time to enter the range. For the spreadsheet example, we are looking for subtotals on the Sales column, Column D. So, the final formula looks like this:

=subtotal(9,D2:D21)

When you first enter this formula in cell D22, it gives you the total amount, the same as when you enter the Sum formula. However, watch what happens when you change the options using the Filter drop-down:

As you can see, the Subtotal formula lets you find totals by Salesperson, Client, Product, etc. quickly and easily. And the total changes with the information you select.

We here at Learn Excel Now hope you found today’s tip on how to calculate a subtotal useful.

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 Date Calculations Part 1: Finding the Difference Between Two Dates

How to Show Formulas in Excel

Excel Formulas: Using Trace Dependents to Find Data Solutions

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

Excel IF Statements Part 2 – Intro to Simple IF Function

How to Calculate a Subtotal In Excel Using the Filter

Sitemap

Connect with Us

Contact