Archive for May, 2012

Introduction to Excel Floor and Excel Ceiling Function

Excel function tutorials

Back in November, I had written a post about the excel round function and had also discussed roundup and rounddown, which you can easily figure out how to use. Sometimes, those 3 are not enough though. I received the following question which I thought was very interesting:

“How can I round to a more precise number? For example, Instead of rounding to the nearest unit or decimal, I would like to round to the nearest 0.05.”

Here is an example of numbers that could be used:

Then, you could use one of the two following functions:

Floor function is the equivalent of rounddown but will round down to that same precision. I personally use it a lot when I am averaging prices to the nearest nickel. For example, Canada is getting rid of its penny. All prices will be rounded to the nearest nickel. Those prices could be either rounded up or down. Here is the result by rounding down with the floor function. I am using the following:


The result is:

Then, I can do the ceiling to round up to the higher nickel as follows:


As always, you can download the spreadsheet here!

How You Can Make More Money With What You Have Right Now


Today my new site, Start Freelancing Now, is going live.

With that being said I wanted to write about a cool financial experiment today. I’m looking to help

My goal is to help you make more money with what you have right now. You don’t have to go back to school to upgrade your skills. You don’t have to network and meet any famous people. You have all you need right now. You don’t need to invest thousands of dollars into a useless business plan.

How can you make more money with what you have right now?

Find potential customers.

Think of all of the ways you can find customers by doing something cool. Below are a few questions to consider.

  • What can I do that nobody else can?
  • How can I help others?
  • What marketable skills do I have?
  • What do I love to do?

This should lead to you finding at least a few potential clients. For example, if you work out often, there are millions of people out there looking to get in shape. There are so many options here. It’s up to you when it comes to what sort of clients you want to target.

Perform a service.

Now you perform the service. This will seem like the most difficult aspect, but it’s not. This is where you make your money. This is where you shine and do what you’re good at.

I can’t help you much at all here. This is where you do your service.

Collect your payment.

The next step is to find a way to collect your payment. How will you get paid? Will you use Paypal? Will you accept credit cards? Will you collect cash?

This is a minor issue, but it can get pretty fun.

My suggestion is that you keep a document on your computer where you track payments.

That’s all you really need to make more money right now.

Are you ready to increase your income right now?

Running Excel Macro Automatically Are On Scheduler

Excel macros

One of the critical reasons why many including myself decide to use excel macros is that it can help make some processes much smoother and faster and it also gives more options.

A lesser known but also very useful function in excel macros is having a specific macro run automatically. There are many different ways to do this but you could have a macro start running at a specific time, on the opening of the file or even have a macro run every X minutes. Today, I wanted to give an example where I want a specific macro to be ran every 5 minutes on my computer.

Why would I need this? There are many different reasons and it could be changed depending on your specific need but I thought it would be interesting to look at this example. There are a few things to know about this before going further:

-This is a fairly advanced excel macro functionnality but I think it’s a good example of how powerful excel macros can be
-Obviously, both computer and the excel file need to be open for such a macro to work
-A macro that goes on automatically can be used to do a lot of different things. However, it does not work well if you are doing other things at the same time

If you are new to excel macros, I’d invite you to look at my introduction post here!

Step #1-Defining variables

Some built-in functions in excel make this macro work more easily, you simply need to “call them” with these variables:

Step #2-It can screw up things if you start several such macros at once. To avoid this, I create a cell that indicates if it’s already running and does not start if it is:

Step #3-When the auto macro is “off”, I reach the auto P&L:

The first line is the name of the macro that I want to execute (or you could even put the actual code)
The 2nd line helps me write down the exact time when the macro was last ran
The 3rd line starts the “timer”

Step #4-There isn’t much to do here, I simply start the timer, write down the time of the “next run”, and also write the status of the automatic macro (“on”):

Step #5-It’s important to also have a stop button:) Here is mine:

Here is what the file looks like:)

You can of course the download the spreadsheet here

How to Kill Distractions Right Now


You want to blog more? Do you want to be more productive with your time? Are you tired of being so behind all of the time?

I know how you feel. I fall victim to distractions all of the time. I go to work on my huge and audacious eBook project, only to find myself vacuuming the place. Sure, the place needs to be cleaned. However, my work needs to get done. I can vacuum later.

Let’s look at how you can kill distractions right now:

Ditch email.

As important as email is, it can be a huge distraction at the end of the day. Email is great for communication. However, you don’t need to be in touch 24/7. You need to get some work done. This is why I suggest you ditch email when trying to work on blog posts or some huge goal.

You go on to respond to an email, then you finish yourself wiring up long drawn out messages just to avoid getting any work done. Typical.

Get off the net.

Once again, as critical as the net is, it can also be a huge distraction. You go on to finish up a blog post, and then you find yourself on Facebook chatting to friends about nonsense. You need to get off the net. Avoid the “Wikipedia” trap where you go online and start researching obscure stats.

If you want to get work done, you need to avoid surfing the net for hours. Facebook is fun for photos. Useless for getting any real work done.

Play music.

Cut the whole world out by justing putting on some tunes. Grab your head phones and get to work. Play your favorite playlist, sit back and enjoy yourself.

What tunes will you blast? What music gets you pumped up for work?

Work in time blocks.

Realistically, most of us just inherently have short attention spans. This is why we need to work with this weakness. Nobody can work for 2-4 hours straight. I consider trying to sit down in a 30 minute block to see how much work you can do.

Working in time blocks helps you get started and build momentum.

Throw out your clutter.

My desk used to be filled with clutter. Now it just has a few books and notepads for me to jot down my ideas. That’s all that I need. All of that clutter would just distract me and interfere with my productivity.

Those are my best tips for ditching distractions to get actual work done. You’ll be surprised when you see what can be done when you live a distraction-free life.

Are you ready to destroy those distractions?

Excel Functions: Count, CountA, CountBlank, CountIf

Excel function tutorials

One of the big challenges when working with databases is to quickly validate the data in order to use the correct numbers when calculating an average. Today, I wanted to look at a few different “count” related functions that you might find useful at one point or another depending on your need. Often, count functions are quite simply a quicker and more efficient way to get data. Imagine that you had a database with names of cities in the US that looked something like this:

I would probably use the excel function “counta” if I wanted to know the number of cities, I could simply do:


Why am I using CountA? Simply because this function calculates the “non blanks”. The function “Count” would actually work if I’m trying to find out how many of these cities have at least 1 pro sports team, I could do:


Then, if I tried to do the opposite for some reason, I could use “CountBlank” which as you can imagine counts the number of cells that do not have anything entered, so I would do:


Finally, I could also want to know how many of these cities have exactly 7 sports teams, in which case I would use Countif:


I could use the same function to find out how many cities have more than 6 teams by simply changing the criteria: