formulas

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

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 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 1 – Intro to CountIF

July 27, 2016 by Tyrone Pernsley

IF Statements in Excel are some of the most useful functions you can use. There are a variety of IF functions and each one can be used for a variety of purposes. Over the next few weeks, we will explore the various IF functions, their uses and how to create them. This week, we are focusing on the CountIF Function.

IF statements are often variations on standard functions, but they allow you to return a value based on specified criteria. The normal Count formula (=count(range)) will return the total number of cells containing data within a range. Here’s a quick demo:

As you can see, it just counted the total number of cells with data.

But let’s say you wanted to count the total sales to a particular company. In this case, you could use the CountIF function to find the total sales to a company.

The syntax for CountIF is:

=CountIF(range, criteria)

So, if we’re going to find the totals to Eastern Company, the formula becomes:

=countif(F2:F21, “Eastern Company”)

And that will the return the specified value:

Note: if the value you’re using as the specified criteria also exists in the table, you can select any cell containing that value rather than writing it out:

The above examples are if the value you’re looking for is an Exact Match. You can also do greater or less than.

Let’s say you wanted to find the total number of sales greater than $300. For that you would use this formula:

=COUNTIF(D2:D21, “>300”)

Now in some cases you might want to it count equal to And less than (or greater than). In those cases, you will add the = sign after you enter the greater than (>) or less than (<) symbol.

So, now that we know the number of cells containing values greater than 300, let’s find the number of cells that are equal to or less than 300. That formula looks like:

=countif(d2:d22, “<=300”)

As you can see, this than finds any cell with 300 or less:

We here at Learn Excel Now hope you now feel comfortable using the CountIF Function. We will be brining you more IF statement tips over the next several weeks so stay tuned!

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 Formula Tricks: Absolute Reference

May 11, 2016 by Tyrone Pernsley

Using Excel to its fullest potential requires knowing how to use formulas. Knowing the different formulas is a great start, but there are tips and tricks you can use to make formulas work even better. In today’s tip, we will explore one of those formula tricks: Absolute Reference aka absolute cell reference.

What is An Absolute Reference in Excel?

This term refers to how you identify the cells being referenced in a formula. A cell reference would be specific cell identified in a formula. For example, if we wanted to run a sum on the following Sale & Tax:

We would enter the following Sum formula:

=sum(A4:B4)

The A4 references the cell with the sale amount and B4 references the cell with the tax amount to give us our total of $318.

Now, if you wanted to copy this formula to find that totals for all of sales, you can copy and paste it down (or use the AutoFill option as in the following animation):

This tool works because of Relative reference. The A4 and B4 in the formula changes to A5 and B5 on row 5, and so on. In this case, the two cells being referenced change according to their relation to where the copied formula is being pasted.

This works well when the information relative to the formula changes in each instance. But what do you do when one of the cells doesn’t change location?

For example, let’s say you wanted to find the commission in the following table by multiplying the sale amount against the commission rate:

This works in the first instance, but then when you try to copy it down, the totals won’t work:

This is because the cell being referenced for the commission rate changes as it is still a Relative Reference.

To make it an Absolute Reference, you use the $ symbol to frame the cell you don’t want to change. Then, when you copy and paste the formula, the commission rate cell stays absolute while the sale remains relative to the row the formula is on:

In this case, the absolute reference anchors one part of the formula so that it can be copied and pasted with ease. This really comes in handy when you have lots of rows of data!

We here at Learn Excel Now hope you enjoyed today’s tip on Excel’s Absolute Reference. This should improve your formulas.

Like Learn Excel Now? Don’t forget to subscribe to our newsletter and follow us on social media!

Kevin – Learn Excel Now

Making Sense of VLookUp: Quick and Easy Tips

April 27, 2016 by Tyrone Pernsley

At Learn Excel now, we are always getting questions about VLookUp. Making sense of VLookUp requires breaking the function down and understanding the key components and what you are trying to find.

What is VLookup?

The VLookup function allows you to find data that is stored in a table from another spreadsheet or a smaller table.

There are 2 ways to enter the VLookUp Function:

Go to the Formulas Tab, click the “Lookup & Reference” dropdown and select Vlookup. This will open the following dialogue box:
You can enter the function as a formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup = TRUE])

Syntax of the VLookup Function:

Lookup_value: The value to search in the first column of the table. This can be a reference or a value.
Table_array: This is two or more columns of data. You can use a reference to a range or a range name. The values that appear in the first column are those searched by the lookup_value. These values can take many forms including text, numbers, or logical values.
Col_index_num: This is the table from which the matching value is returned. There are several different scenarios for the value’s placement:

If the col_index_num is less than 1, then the vlookup shows the error “#Value!”
If the col_index_num is greater than the number of columns in the table_array, then the vlookup shows the error “#REF!”
If the col_index_num is 1, then the value will be in the first column of the table_array.
If the col_index_num is 2, then the value will be in the second column of the table_array (and so on)

Range_lookup: This function is meant to specify whether vlookup should find an exact or approximate match of the value you search.

If “true” or 1 is used, then an exact is returned unless there is not an exact match, in which case the next largest value that is less than the lookup_value is returned.
If “false” or 0 is used, then an exact match is returned. If there are two or more values that are an exact match, the first value found is used. If no exact match is found then the error “#N/A” is returned

S0, what does that all mean?

Let’s say you wanted to find the total profits from sales to XYZ Company in the following table:

Now, in this simple table, you could just sum the individual cells under the Profit heading that correspond to XZY Company. But, if this table were hundreds of rows long, that would be impractical.

So, we are going to use VLookUp:

=VLOOKUP(“XYZ Company”,A1:E19,5,)

In this formula:

XYZ Company Acts as the Lookup_value
A1:E19 is the table_array
5 is the col_index_num
Because we are just looking to return a value, there is no Range_lookup required.

Other Helpful Tips:

You need to use “Ctrl,” “Shift,” and “Enter” to create brackets around an array formula rather than simply using “Enter.”
Keep in mind that table_array doesn’t differentiate between uppercase and lowercase letters.
Put the value of the first column of the table_array in ascending sort order to ensure that vlookup gives you the correct value. To set this, click on the “Data” icon on the tool bar, then select “Sort,” and finally select “Ascending.”
If you get the error message “#N/A”, check to make sure that the lookup_value is not smaller than the smallest value in the first column of the table_array.

We here at Learn Excel Now hope you now feel more comfortable using VLookUp. It is one of the most widely used Excel functions but it is a complicated tool. Making sense of VlookUp requires practice and use.

Like Learn Excel Now? Follow our social media pages and share our Excel content with your networks!

Kevin – Learn Excel Now

Basic Calculations in Excel: How to Quickly Find the Answers You Need – Video

February 17, 2016 by Tyrone Pernsley

Excel has tons of formulas, functions and features to make calculations for your data. But you can also do basic calculations in Excel without using formulas. Basic calculations in Excel are great for quickly finding the results you need – be it addition, subtraction, multiplication or division – without having to enter any clunky formulas (or remembering them!)

LEN-WebVideo_BasicFormulas from Learn Excel Now on Vimeo.

Please enjoy this video on basic calculations where you will discover:

How to set up basic arithmetic in a cell
How modifying a cell in a formula will affect the answer

As you can, basic calculations in Excel are quick and easy. Here are a few keys to remember when doing basic calculations in Excel:

All formulas begin with the equal sign: =
For addition, you want to use the plus sign: +
For subtraction, us the minus sign, also the hyphen: –
For multiplication, use the asterisk (shift+8): *
For division, use for forward slash: /

Basic calculations in Excel are perfect when you need to find a result quickly and easily. For example, if you have two rows of sales totals and want to know their total sum, you can enter:

=(Cell1+Cell2)

Or let’s say you have a revenue and one column and cost in another want to find profit, you would enter:

=(Cell with profit total – Cell with cost total)

Another key to remember is that all cells in Excel have an alphanumeric designation; where the cell begins with the Column identify letter and ends with the Row identifying number (e.g. A1, B2, etc.)

Using the cell destination, rather than value in the cell, allows to the outcome of the formula to change if the cell value is changed.

You should be all set for using Basic calculations in Excel!

We at Learn Excel Now hope you found this week’s tip useful. Please subscribe to the newsletter to stay up-to-date with our latest tips, guides, tutorials and training!

Like Learn Excel Now? Get social with Excel! – Share this post with anyone needing Excel help on social media.

Kevin – Learn Excel Now

Excel Analytics: Creating Tables for Your data

February 3, 2016 by Tyrone Pernsley

There are a variety of ways to sort and organize your data in Excel. These features allow you to quickly process data to get essential analytics, statistics, trends and other information to inform decision-making. One feature is using Excel tables. Excel Tables are great way to quickly and easily organize and display your data. The following quick guide will provide you with tips, so you can start creating Tables for your data.

Creating a Table

To create a table, first highlight the data that you want included, then go to the “Insert” tab on the top toolbar and select “Table.”

Your table will be created, and you will automatically be in the “Design” tab.

Now, you can choose how you want your table to look by choosing from the different table styles. You can choose from a variety of different colors and then you can choose color density from light to dark. There is also a quick shortcut that you can use to insert a table. To use this, highlight your data, and then click “Ctrl” + “L.” Your table will be created automatically and then you can design it.

Filtering and Sorting the Data in Your Table

Once you have created your table, each column heading will have a drop-down box that automatically appears in the column heading in the top row. The down arrow key allows you to open the drop-down menu.

You have different options to sort the data in the drop-down box. From there, you can sort the data alphabetically, by highest or lowest value or by color.

To filter your data, you can customize number or text filters by specifying the values that you want hidden or shown. You can also exclude values that you don’t want show by unchecking the check box next to the value(s) that you want hidden.

Making Calculations in Your Table

You can use calculations to find the totals, averages, and various other calculations for columns or rows. To do this, you have two options. First option: you can click on the cell at end of the column or row and click on the “AutoSum” button.

The other option is to select the cell at the end of the column or row, then go to the “Formulas” tab and select the formula that you want to use. When the formula is added to the table, a drop-down arrow is automatically inserted to the right of the value. From the drop-down options, you can choose different formulas to perform other calculations on that row or column.

We here at Learn Excel Now hope you now feel confident in creating tables for your data in Excel. It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work. Subscribe to our blog to receive weekly Excel tips. If you’re looking for more in-depth training check out our upcoming instructor-led, live online training.

Liked this Excel Tables quick guide? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Learn Excel Now

How to Use the Excel MEDIAN Function

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 IF Statements Part 3 – Nested IF Statements

Excel IF Statements Part 1 – Intro to CountIF

Excel Formula Tricks: Absolute Reference

Making Sense of VLookUp: Quick and Easy Tips

Basic Calculations in Excel: How to Quickly Find the Answers You Need – Video

Excel Analytics: Creating Tables for Your data

Sitemap

Connect with Us

Contact