Category Archive 'Excel function tutorials'

Nest if Functions – It’s All About Doing It Step By Step

Excel function tutorials

I often get these types of questions where someone is trying to do a nested if function but having trouble doing so. Here is one such example:

“I am trying to write a and/if statement to read if b2=”manager” and d2=”b” and h2

Doing An IRR function in Excel manually Using The Goal Seek Function

Excel function tutorials

The other day I got an interesting question from a reader. He was trying to do an IRR (internal rate of return) but had one specification. An IRR function assumes that all cash flows are at the same period of the year. He wanted something more precise that would account for the fact that some cash flows might be at the start, beginning or middle of the year. I suggested that he try to do an IRR manually and decided to give it a try myself.

First, take a look at a traditional IRR function result:

Then, I decided to get the same result but with a function instead. How?

An IRR function is the rate of return for break-even. So I created a formula manually that would calculate the “NAV”. When that NAV would become $0, I would have the correct IRR. Look at this screen:

Then, I used the goal seek function to set the nav to $0.

As you can see, I get the same result:

Then, I did the same exercise but changed the years to reflect the period. So Q1 of the 3rd year would become 2.25, etc. See the result here:

AverageIf Function Using Excel

Excel 2013, Excel function tutorials

I’ve heard from several of you that use Excel to manage large sets of data nad I know hata few of you must then get different metrics from that data depending on specific conditions. I’ve already covered Sumif quite a bit so today I thought I’d get into “averageif”. Let’s start with a large set of data:

This is an example of the top rankings in the WTA tennis tour. One typical thing that you could end up wanting to do is find information such as:

-What is the average # of tournaments that the top Russians have played compared with the top Americans. Here is How I would do it:

I’m certain that many of you can now imagine how this could be used.

Reader Question: Complex Nested If/And/IfNot Functions

Excel function tutorials

I received a question along with the following table:

The question is as follows:

“IF the Transaction is a ‘delete’, AND the Rate Category is selected, THEN match the delete rate from the corresponding Rate Category List into the Rate cell, IF the Transaction is an ‘add’, AND the Rate Category is selected, THEN match the add rate from the corresponding Rate Category List into the Rate cell.”

Again, it’s about trying to go step by step. the first case here is:

IF the Transaction is a ‘delete’, AND the Rate Category is selected, THEN match the delete rate from the corresponding Rate Category List into the Rate cell

So here, I’d need an “if and” condition that will check the value of the first column, look if cell 2 is empty and if both conditions are met, do a vlookup to find the correct rate. Simple? Here is what I came up with:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),””)

The second part is:

IF the Transaction is an ‘add’, AND the Rate Category is selected, THEN match the add rate from the corresponding Rate Category List into the Rate cell.

Here I will use a similar function of course:

=IF(AND(A14=”Addition”,B14<>“”),VLOOKUP(B14,RSUI_Adds,2,FALSE),””)

I can then combine them together as follows:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),IF(AND(A13=”Addition”,B13<>“”),VLOOKUP(B13,RSUI_Adds,2,FALSE),””))

One last change I will make is to add an “Unknown” if the first column is filled but not the second by changing the formula to:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),IF(AND(A13=”Addition”,B13<>“”),VLOOKUP(B13,RSUI_Adds,2,FALSE),”Unknown”))

The end result is:

As you can see what looked very complex ended up being rather simple:)

Reader Question: Nested If Condition

Excel function tutorials

One of the more recent questions that I got on the blog was the following:

I need some help.
I want to use an if statement for the following:
Quantity (d1)
Pk Qty (E1)
Total (F1)
UOM (AA1)

Formala Help to be entered in F1:
If pk qty = 0, then enter the value for d1 in F1, If Pk quantity is>0, then return D1*E1 unless UOM =FT, then enter D1

Let’s try ot resolve this one step at a time

First off, because I’m visual, I’ll put this into a spreadsheet:

Then, step by step:

“If pk qty = 0, then enter the value for d1 in F1″

=IF(E2=0,D2,””)

For the second part:

If Pk quantity is>0, then return D1*E1 unless UOM =FT, then enter D1

=IF(AA2=”FT”,D2,D2*E2)

Then, I can simply enter the 2nd part into the first formula:

The end result?

=IF(E2=0,D2,IF(AA2=”FT”,D2,D2*E2))

Once again, the key is doing this one step at a time, it’s that easy:)