One of the questions that we have been receiving quite a bit in recent months has been regarding the difference between the Sumif and Vlookup functions in excel. They are fairly different but there is some confusion regarding their use. Let’s start off by explaining both:

-Sumif function: Is used to sum the quantity (or any other data in a separate column) for a specific criteria. We already wrote about this function, you can find out more about the sumif and countif functions
-Vlookup function: Is used to find a reference associated to a specific criteria

These are the formulas used:

=SUMIF(A:A,”Strawberry”,B:B)
=VLOOKUP(“Strawberry”,A:B,2,FALSE)

When do you use each one?

Vlookup

-If you only have one sample for each “fruit”
-If the data you are looking for is not a number
-If you want to easily spot a “missing fruit” – vlookup will give you a “N/A” result when a vlookup returns no results

Sumif

-If you have more than one sample
-If you want to avoid getting “N/A” errors that will screw up calculations in your spreadsheet

Does that explain it well enough?

Popularity: 1% [?]

If you are like me, you have a few excel spreadsheets that are becoming larger by the day. It can become very complex to keep up and you will often use functions such as the “match” function that we discussed last week. Another function that can help when you are using reference functions is the offset function. We used it in last week’s post but it was done rather quickly. Here is a better example:

As you can see, if each product has 2 regions, how could you find what those are and what the prices are? A vlookup would not work because of the fact that the result is on a different line. In order to proceed, we will use the match function but also the offset one.

Here is the formula for the offset function:

=OFFSET(reference, rows, cols, height, width)

In this case, we will need to find “orange” through the match function and then use the offset to find both locations. This could work on a much larger excel.

Where is it? =MATCH(“orange”,A:A,0)
Location #1 =OFFSET(A1,MATCH(“orange”,A:A,0),1)
Location #2 =OFFSET(A1,MATCH(“orange”,A:A,0)+1,1)

When you are unsure about how a file will evolve, if columns or ranges will be moved around, using the match or offset function will make it much easier to maintain the files over time.

Popularity: 1% [?]

In our past posts, we have looked at different ways to use reference functions in excel. These are often used in order to help retrieve data in large sets. We have obviously looked at using the Vlookup function but also Sumif, Hlookup, etc. All of these can often be used but in some cases, having access to another function can also be quite helpful. In this case, we are looking at the match function in excel.

What is the Excel Match Function?

It is a function that gives you information about where a specific data point can be found in a database. For example, imagine that you had 10,000 lines in a file and were trying to find a specific data set. There are many different ways to get this done (as is always the case in excel it seems but the match function can give you an easy way to find out where a data lies in the spreadsheet. You can of course use nested functions in order to get additional data.

Suppose that you have a line of data and you need to know where you can find a specific fruit. Here is the data:

I could simply apply the following formula:

=MATCH(“peas”,A:A)

This would return: 10 which indicates that the value is in the line #10. It is not case sensitive (contrary to vlookup) which can also be helpful.

If you wanted to combine a nested function to find the price, we could use this new data:

By using the “offset function”, I can get the price as well:

=OFFSET(A1,MATCH(“peas”,A:A)-1,1)

As you can see, in this case, I could have used the vlookup function as well but it was more practical.

Hopefully this helps! As always, be sure to drop by with any comments or questions regarding this or any other excel function/issue!

Popularity: 1% [?]

Over the last few weeks, we have taken the time to look into various uses of some common excel functions. Often, you will not necessarily want to use it in the exact same way but it might give you different ideas and it is often much easier to understand such functions with more concrete examples. In this case, we take a deeper look at the NPER function which calculates how much time will be necessary to either pay back a debt or accumulate a specific amount if circumstances (payments, interest rates, etc) remain constant. It is usually the most realistic way to do things anyway.

In today’s example, we look at a very common scenario. How much time would be required for a consumer to pay back his credit card (or other) debt by paying only a portion of what is owed. Of course, this scenario is often the type of reality test that motivates credit card holders to stay far away from credit card debt. Why? Let’s just say that the 18% or so interest rate makes it very difficult to pay back a large amount.

For the NPER function, what is required is:

-Initial amount: $25,000
-Interest Rate: 18% annual which we will convert to a monthly rate by doing:

=(1+18%)^(1/12)-1

Payments made each peeriod: $350

Then, we will simply compute the NPER function. Take a look at our screenshot:

We used: =ROUND(NPER(C7,C9,-C4,0),1)
Then we changed the result into years= =ROUND(C11/12,1)

It is that easy. For those curious to see it in action, you can download the spreadsheet here. Also, here are the results using a couple of different scenarios:

Paying $500 per month instead of $300:

And with a rate of 13%:

Popularity: 1% [?]

I have to say that I’m thrilled because we are receiving quite a few questions these days both in comments and by email. As always, we are more than happy to help and in most cases, the answer can help quite a few others so we publish the answer as a new post. We’d be very happy to receive questions from you so be sure to contact us with any problems that you have. Today, we received the following question, which initially appeared as a comment on our excel nested if statements tutorial. I’ve translated the question to make it easier to understand here:

I am just trying to get a Y or N when applying this formula:
=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”))
Below are a couple of sets of data and the results that I got when the formula was applied:

As you can see, once the data does not meet the first part of the formula, it gives you a FALSE. If it meets the first part but does not meet the second, it gives you an N. If it meets both parts, it gives you a Y. I only want the formula to produce 2 results, Y or N. Let me know if you have any further questions. Otherwise, let me know if you can help me solve this problem.

There are a few different things to discuss here. First off, let’s translate this into an easier question to understand. Let’s imagine the question is. I want to determine small or large schools that have very poor or very good results. A small school would have less than 450 students, a large one over 550, and very poor results are under 50% while a very good one is over 70%.

So what am I looking for?

Schools that have under 450 or over 550 students that have a score under 50% or over 70%

The first thing that I notice in the statement is that it is incomplete:

=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”))

This statement initially does one thing. It checks if U2 is greater or equal to 500 or smaller or equal (of course, any number will fit this condition). If that condition is met, it checks for the 2nd part, which is similar. The problem of course is that it does not give any indication for what happens if the first condition is not met. I would thus start by changing the statement to:

=IF(OR(U2>=500,U2<=-500),IF(OR(V2>=10%,V2<=-10%),”Y”,”N”),”N”)

This fixes the problem of getting a “False”, see the new result:

In order to add the conditions that I discussed, I would change it to:

=IF(OR(U2>550,U2<450),IF(OR(V2>70%,V2<=50%),”Y”,”N”),”N”)

By changing the set, you can see the result:

Hopefully this helps. It’s important to always structure your arguments to not miss any parts. Doing one condition at a time is a great way to do get it done. We look forward to getting more questions from you!

Popularity: 1% [?]