Office

CAGR in Excel - Dataset

3 ways to calculate CAGR in Microsoft Excel

You’ve come across the term CAGR and want to know how to calculate it in Excel? This post gives you three different ways to do so in Microsoft Excel. But, first, let’s understand what CAGR is.

Advertisements
INDEX MATCH Step 1

Excel Tutorial: INDEX MATCH primer

Two weeks, we looked at the syntax of VLOOKUP, INDEX and MATCH. Last weeks tutorial should have walked you through the basics of VLOOKUP. In this post, we’ll use a similar file from the VLOOKUP tutorial to find a value from the table using INDEX and MATCH.

Source table

If you haven’t had a chance yet, please go through the VLOOKUP tutorial. Alternatively, if you’re ready to jump right into INDEX/MATCH, then let’s get started.

Firstly, create the above table in Excel. I’ll be using Excel 2010 in my example. You can also download the examples file before you proceed. The file contains a Questions sheet which you can practice in and a Solutions sheet with the answers.

If you haven’t had a chance to review the basic syntax of the functions, please do read this post before you continue.

Read More »Excel Tutorial: INDEX MATCH primer

VLOOKUP Step1

Excel Tutorial: VLOOKUP primer

Last week, I introduced you to VLOOKUP, INDEX and MATCH. In this post, we’ll use VLOOKUP to find a value from the table.

Source table

Firstly, let’s create the above table in Excel. So whip up the installation of Excel you have. You can use either of Excel 2003, Excel 2007 or Excel 2010. I’ll be using Excel 2010 in my example. Download the examples file before you proceed. The file contains a Questions sheet which you can practice in and a Solutions sheet as well.

If you haven’t had a chance to review the basic syntax of the functions, please do read this post before you continue.

VLOOKUP is the easier function of the two to use and understand. It takes just 4 parameters and we’ll be using all 4. And, we’ll look at 4 options of using VLOOKUP.

Read More »Excel Tutorial: VLOOKUP primer

Schedule Office Shifts Online with FindMyShift

Working at the call center was one of the best experiences I have ever earned. Like any other company we also had issues and the one that annoyed me the most was the one with our scheduling. I don’t think anyone appreciates the fact when your boss calls you at an unexpected time and tells to show up at the office in a different shift.

Excel Tutorial: SUMIF with Multiple Conditions

The title of the post is a bit of a misnomer because the SUMIF function in Excel does not allow you to have more than condition.

Excel 2007 introduced the SUMIFS function which allowed for multiple conditions. However, if you are using any version prior to Excel 2007 or if the persons who will be using your Excel workbook will be using a version prior to Excel 2007, then the SUMIF function will throw up an error.

That is a problem a colleague faced at work. To solve this problem you can use SUMPRODUCT along with double negation. The double negation is simply two minus signs one after an another. The net effect is that it doesn’t change the value of the calculations.

The reason we use the double negation is because, Excel does not always understand that TRUE=1 and FALSE=0 when you use SUM or SUMPRODUCT.

Read More »Excel Tutorial: SUMIF with Multiple Conditions