o explain very briefly, a few months ago I made a purchase with my parents. They paid the condo in cash and I was left with my part that I would pay them 5 years later without interest (good deal hey?). So I’ve been saving money every month and investing it in a few stocks. I use a very simple spreadsheet to calculate how much I’ll need to save every month. If my money was invested in a fixed rate account, that number would remain the same. But since my investments values increase and decrease every month, the number actually changes.

I will use the PMT function to calculate what I need. In order to get started I will need:

Rate = my expected return (I will use a monthly return since I’m looking for a monthly amount)
Nper = how many months between now and me paying that amount back
PV = how much I currently have in that account
FV = how much I’ll need to pay at the end

Rate: I will assume a 7% annual return. To get the monthly return, I’ll simply do:

=1.07^(1/12)-1

Nper = 47 months
PV = $13,000
FV = -$50,000

You can see both the table, formula and answer here:

Simple enough? It gets better. Since I linked my PV amount to a page where I get live stock quotes, that numbers can become “live”. You can download the spreadsheet here.

Once in a while I get asked some more complex nested if/and questions. In almost all cases, it is all about defining the problem in a simple way and building the formula step by step. Here is one question I received a couple of weeks ago:

If I redefine this problem. I have 4 values. In order for the entire problem to be true, 2 conditions must be met. And each of those conditions can be accomplished with 1 of 2 possible values. So let’s go ahead and try this out:

I will start at the end. If A=C or C=D true, that condition will be met. Here we go:

=IF(OR(C2=C4,C4=C5),1,0)

Basically, if one of the conditions is met, the formula will give me 1. I will do the same thing for that other part.

=IF(OR(C2=C3,C3=C5),1,0)

Now, I must combine these 2. Basically, if the sum of both is 2, then the overall condition is met. If not, it is false. Here is how I’ll do it:

=IF(IF(OR(C2=C4,C4=C5),1,0)+IF(OR(C2=C3,C3=C5),1,0)=2,”True”,”False”)

You can see the result which is false of course:

If I change B and C to match A, I should get a “true”

And here I go:

Of course, this could be done in many different ways but I just wanted to show another way. Hopefully this helps!

Over the past few weeks, I’ve been getting an increased number of questions regarding the use of pivot tables. Believe me, I understand. As much as I know how useful and powerful pivot tables can be, I resisted using them for years. I never felt comfortable using them and always looked for alternative methods. Thankfully, I’m getting much better at it. Today, I wanted to look over a few basics because compared to other types of excel functions, using pivot tables is not as easy to grasp initially. What is a pivot table and when should you use it?

Pivot tables are a way to express lengthy tables of data in a simple way. It helps to draw conclusions about the data, etc. In almost all cases, there are alternative ways but pivot tables are very easy to use once you’re used to it.

What do you need? Simple.

-A table with multiple columns that have headers at the top (it simply will not work without headers).

Ideally, you start by finding the place where you’d like to place the pivot table. It can be on a separate sheet (which works very well actually) or anywhere on an existing one.

Here is an example of a table with different stocks and relevant info about each one:

So then I went to a different sheet and clicked:

Insert/PivotTable

Then, I was asked to select a table:

As you can see, I am now able to see a basic table and must now go to work. Then I would select on the right the columns that I’m looking to add. You would drag the categories to the appropriate location at the bottom. For example, if I’d like to know how many stocks of each category I have, I would do the following:

And have this result:

I could also get the market cap per category by adding it to the values section.

As you can see, the result is very interesting:

I hope this helps a bit. I do encourage you to get a table of data and start playing with it, it’s the best way to get yourself familiar with the power of pivot tables. You can also download my spreadsheet here

I’ve done a couple of similar examples but here is one question I got a few days ago:

I am trying to write a formula to pay a percentage up to a certain amount and then increase the percentage once a rep exceeds that amount. Ex: Rep brings in $2,000.
Pay 50% for the first $600
Pay 60% for the next $600
Pay 70% for the next $600
Pay 80% for everything over $1,800.

How will I get it done? Let’s start from the beginning. If my sales number is in cell C6, then I’d start off by checking if the sales is above $1800. If it is, then I’d simply add:

50% of $600+60% of $600 + 70% of $600 to the 80% of everything over $1800. In short it would be:

=IF(C6>=F9,1080+(C6-F9)*H9,1)

As you can see, it works well for that kind of number:

But would not work for a number under $1800:

So I will then work my way to that category:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),2))

Of course, I must also adjust for numbers in the second category, I will do so with the following:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),3)))

And finally, I can replace the “default” here. If the number is not in category 2, we can assume that it is in category 1 (less than $600 in this case) so I simply replace 3 by:

=IF(C6>=F9,I6+I7+I8+(C6-F9)*H9,IF(C6>F8,I6+I7+H8*(C6-F8),IF(C6>F7,I6+H7*(C6-F7),C6*H6)))

And here you go, it works and easy to adjust. You can modify the percentages, levels, etc.

You can download the spreadsheet here!

It is very possible that you have heard about an API at some point in the past. Others might be asking what in the world I’m talking about. An API is an IT term that is basically a protocol that makes it easier to interact with different sets of data. Most big companies such as Google, Twitter, Facebook, Amazon, etc all have their own API’s. Why? There are many reasons but let’s focus on the one I’m most interested in. They want to make their data more easily accessible by outside applications.

For example, seeing data about the weather, search results or twitter updates is all possible through the web. What if you’d like or need to access that info from some other type of program? Obviously, they will not just let you connect to their database and start making queries. Instead, they build a protocol or interface that makes it possible to extract the data. The main benefits are the saved time, the (fairly) easy use, etc. The downsides is that in most cases, heavy use will end up costing you money or being regulated at some point. Twitter is one of the more used API’s in the world and used to be free and unlimited but in recent months, Twitter has decided to charge for those that use over 100,000 queries per day or more (I believe that was the criteria). It’s not something I worry about at this point but it’s certainly worth considering:)

So today, following an example given by Microsoft, I signed up for the Wunderground API, which is free at:

http://www.wunderground.com/weather/api/

Once I had signed up and received my key, I’m all set to start using the webservices function in Excel 2013 to make “better” web queries!

Unfortunately, when I tried, I ended up getting an error in my response so I will need to do further research. I’ll certainly keep you posted as I do intend to have this fixed soon:)