Category Archive 'Excel spreadsheets (.xls)'

Vlookup Function About To Get Much Easier As “Relationships” In Excel 2013

Excel spreadsheets (.xls)

Vlookup functions are difficult to understand initially and while they become very powerful once you do fully understand how to use them, I can tell you from the dozens of requests that I get every month that the concept remains very difficult to understand. Microsoft is trying to make life easier for users by changing the concept again. Here is the direct quote from CFO’s excel 2013 preview:

Relationships instead of VLOOKUP. If you add your worksheets to the pedestrian “Data Model” feature, you can use the Relationships icon to define that CustID in your million row transaction worksheet is related to CustNumber in your customer worksheet. Now, without doing millions of VLOOKUPs, you can create pivot tables from the data on both worksheets. Whether you’re sick of people who feel superior because they can do VLOOKUP, or someone who does VLOOKUP in their sleep, no one can argue that creating a relationship in 3 clicks is faster than waiting for a million VLOOKUPs to recalculate.

This might work although it will simply make other parts more difficult. I’m assuming that having the right column headers will become very important as Excel 2013 will try to guess how data interacts with each other. It will certainly be very interesting to see. I’m curious, do you expect to upgrade to Excel 2013 as soon as possible? I will certainly start moving soon although I do use cloud computing a lot more than I did just a few months ago so it will only impact my more complex spreadsheets. If ever you’d like to access a preview version, you can try going here.

Rolling Average In Excel

Excel spreadsheets (.xls)

One of the most researched numbers for stock investors and traders is a moving average. What is it? The average end of day price for a stock in the past 30 days for example. Why does it matter? Because many investors consider that breaking through that average is significant. Let’s take a look at how I’d calculate that price. First, I downloaded the stock price closes from Google Finance for Apple (AAPL) as you can see here:

Then, I added a column for the 30 day moving average and simply used the “average” function:


Then, I dragged the formula down as you can see here:

Then, I thought it would be interesting to determine days when the stock moves through the rolling average. How? First, I will determine if the stock is above or below the average by using an “if function”:


Then, I can simply make it a “nested” function, by checking if the previous day had the same result. If it didn’t, that means the stock broke through its rolling average. I will simply change it as follows:

=IF(B2>C2,IF(B3>C3,””,”Stock Breaks To The Upside”),IF(B3

You can also download the spreadsheet here

Scenario Analysis In Excel 2007

Excel spreadsheets (.xls)

One of the more interesting features in Excel 2007 is the ability to quickly get decent analysis for different scenarios. Take an interesting case where I have this data for company XYZ. I’m asked to calculate the Net Present Value of this project/company over 5 years using that data. I will create a second table below with the data. By doing this, I will be able to easily see the impact of changing my assumptions. Here is what it looks like:

Most of the data in the tableis fairly straightforward to calculate. Unit sales are given in the example and increase by the sales growth number (C6) every year. Prices are also given with growth rate. The interest rate is necessary to calculate the NPV obviously (you can get all formulas and data in the spreadsheet, link is at the end of this post).

Basically, as you can see, the NPV in my current scenario is $134K or so.

Let’s suppose that for a few of these assumptions, the estimate is very uncertain. In my example, let’s suppose that the sales growth could change quite a bit. One thing that I could do is build different scenarios where I will change both that and the cost growth. I will name the scenarios. How do I do it? I will go into “Data/What-if-analysis”

Then, I select scenario manager and I can create different outcomes. How? I simply click “add”:

Then I can name that scenario, select the cells that I will be changing (ideally the same ones as other scenarios):

Then, I will be asked for those values:

Once I’ve build all of my scenarios, I can click on “summary” and then see the results:

Here is the result that I get:

I could then create charts, etc It can be incredibly powerful.

You can download my spreadsheet here for a better look at my scenario analysis.

Transition From Excel 2003 to Excel 2007

Excel spreadsheets (.xls)

I’ve talked about the upcoming cloud based Excel that will be launching in a few months and I’m certainly excited to try out the upcoming version. That being said, I’m not exactly running the most recent version of excel. In fact, I currently use Excel on a few computers:

-Work computer: Excel 2003
-Home computer #1: Excel 2007
-Home computer #2: Excel 2010
-Wife’s Mac computer: Excel for Mac

This week, they upgraded my Excel at work from 2003 to 2007 (enterprise is a always lagging a bit) and I was reminded of how drastically different those 2 are. Despite already being an Excel 2007 user, I was caught off guard. So today I thought I’d go over a few of the excel options that I looked into to get me comfortable again:)

The big difference of course is that the top menus are completely different (the ribbon menus they are called) and I would argue that more clicks are required and I sometimes feel like I lose time using it. So first off, where can Excel options be found?

First off, I decided to change the color scheme to add a bit of character, see how I did here:

Here is what it looks like:

The critical part in my opinion is finding the excel functions that you use most often and putting an icon on your top bar through the options:

You can add them here:

For example I added the “sort” function here:

Once you are able to add the most popular functions, you suddenly start seeing that Excel 2007’s power makes it a no-brainer upgrade. It’s faster, manages data a lot more efficiently and is just a big upgrade overall.

Have you upgraded to Microsoft Excel 2007? If so, how did it go?

Two Experiglot Offerings – eBook And My Help For Your Spreadsheets

Excel function tutorials, Excel macros, Excel Optimization, Excel spreadsheets (.xls), Excel Test

Over the past few months, I’ve been interacting with many of you who have been visiting Experiglot, commenting on posts, sending me emails with questions and comments, etc. I’ve came to the conclusion that there were two big needs and today’s post is to announce that I am finally able to help out on both.

#1-Improve Your Excel Skills

Many of you have projects related to work or hobbies that require using excel either to improve, automate or even just to track and organize data. Searching this wensite and the web in general is great but we sometimes lack structure or don’t know what to look for. I’ve created an ebook that includes tens of functions along with a few spreadsheets wwith examples of vlookup, sumif, macros, etc. It’s a fairly large package that can enhance your excel skills and knowledge significantly, all for a very reasonable $9.95!!!

Find out more about the ebook here

#2-Get Instant Help For Your Spreadsheet

I know the feeling. You’ve been trying to get this thing done in excel for hours and maybe even days and it’s just not working. At this point, you need to get it done. I would be more than happy to help. You can send me your spreadsheet along with a description of what you’re trying to do. For a modest fee, I’ll take a look and get back to you within 24 hours. In over 90% of cases, you’ll have it fixed while more complex spreadsheets might require additional work or be beyond the scope of what I can reasonable expect to do.

This is guaranteed. If the spreadsheet does not do what you were looking for, I’ll be more than happy to pay you back.

Find out more about getting my help for your spreadsheets here