Working with excel functions is not easy to learn and usually requires a decent amount of practice. Every once in a while, I try to publish some examples sent by some of you and here is the latest one that I got:

I want correct formula for following:

I want a result in cell c3 of following
1. if b2 is >= 1200 = “A+”
2. if b2 is >= 976 = “A”
3. if b2 is >= 751 = “B”
4. if b2 is >=601 = “C”
5. if b2 is <600 = "D"

As is often the case, the trick here is not overcomplicating things. You can do one step at a time and get to the answer.

Step #1-Determine if the grade is A+

=if(b2>=1200,”A+”,”zzz”)

It’s important to understand this part. I’m simply looking to see if the grade deserves an A+. If not, I replace it with zzz until I can work out the other conditions.

Step #2-Determine if the grade is A. I start off with the previous formula:

=if(b2>=1200,”A+”,”zzz”)

And I replace “zzz” with a condition that will verify if the grade should be “A”

=if(b2>=1200,”A+”,”zzz“)
=if(b2>=1200,”A+”,if(b2>=976,”A”,”zzz”))

Can you see what I did? I replaced the bold part of the first formula by the bold part of the second. Thus, I can already see if the grade should be A+ or A. I will continue

=if(b2>=1200,”A+”,if(b2>=976,”A”,”zzz“))
=if(b2>=1200,”A+”,if(b2>=976,”A”,”if(b2>=751,”B”,”zzz”)“))

Then one more step:

=if(b2>=1200,”A+”,if(b2>=976,”A”,”if(b2>=751,”B”,“zzz”)”))
=IF(B2>=1200,”A+”,IF(B2>=976,”A”,IF(B2>=751,”B”,IF(B2>=601,”C”,”D”))))

That’s it:) Simple enough?

I’ve been discussing annuities quite a bit in recent days so i thought it would be interesting to give a shot at calculating the value of an annuity in excel. This is obviously not a perfect method and I’ll try to give you an idea of things that could be done to improve on the calcs but it does give you a good idea.

This is a very simple annuity where someone pays an amount to start receiving an annual amount at a given age until he or she dies. So basically I want to determine the cost. I need to know what the annuity would be paying at that point every year, if the amount will be adjusted for inflation, etc.

I first looked for data on life expectancy which ended up being available per country (it does of course depend on being a man or woman). Here is a sample:

Then I entered a place for assumptions:

And finally a place where I did all of my calcs:

I used two excel functions that I’ve used here before, vlookup and PV. You can see the whole details in my spreadsheet. Here is a snapshot of my answer for my initial assumptions:

I also did a tab where I prove my calcs. To be fair, there are several things that could be improved:

-life expectancy will be higher by the time a current 32 year-old dies so that number should be boosted
-other factors would help to determine if the person will live a long life (general health, etc)
-inflation and market returns could certainly be changed
-etc

You can take a look at the spreadsheet here

One of the primary ways that I use Outlook to save time at work. I’ll try to discuss some of the things that I do in upcoming posts but today I wanted to start with a fairly simple one. What is it?

Every day I send out emails with a postponed date. It’s a reminder to myself or others in the team of specific events, things to do, etc. Yes, I know that reminders can work but I personally feel like sending emails is much more effective.

So I use excel to send out these requests, it’s much easier to do and I’m able to send out tens of different ones in just a few minutes…! Take a look at the most basic spreadsheet:

Here is the code that I used:

One I click on the “send” button, the email appears in my outlook outbox:

If the date and time is already past, it is sent automatically and would appear in my sent items. There are many possibilities here such as using it for email reminders, sending emails later (no matter what the reason), etc.

You can download the spreadsheet here

Suppose that you have a few hundred cells where you need to make a small change. Chances are that you will either require the help of a macro or an excel function in order to get it done. I was given a fairly simple task. A reader had a list such as the one you can see here:

He had to replace the “-“ by the following “.-.”. Basically, add a dot before and after. There are a few ways to get it done but doing it by hand was impossible (there were about 1000 different cells that needed to be changed). So what could I do? I decided to use the replace function. Herei s how it should be used:

=replace(old text,start num,num_characters,new text)

So basically for the first cell, I needed to do the following:

=REPLACE(A1,FIND(“-“,A1),1,”.-.”)

Why did I use “find”? Because I needed an easy way to find where the “-“ was located. You can see the end result here:

The replace function is fairly easy to use and can turn out to be very useful! You can download the spreadsheet here

One of the more common questions that I get is managing dates in excel. I’ve already discussed the challenges of date formats but another common use is managing dates when you need to know what the next business day will be. Why?

Because you need to know if that next day will be:

-a weekend
-a holiday

Suppose you have a cell where you write today’s date by using the today() reference as follows:

Then, in the next line you need to know what the next business day will be so you do:

=TODAY()+1

And in that specific case, it would work. But that formula would not detect what type of day it is. Of course, I could use formulas such as =day(D1) which would tell me which day of the week today is.

Even better though is simply using the “workday” formula which will take out weekends.

Then, I can a holiday calendar that I will refer to in that formula as you can see here:

Easy enough? Let me know if you run into issues with this!