Today, I wanted to give an example of one very common problem that can be automated thanks to excel. Let’s imagine that you are a pool cleaner and you have a file with the name and emails of your clients (or addresses) in order to automatically create one file per client that can then be printed and sent. In theory, it could even be automatically emailed to each client just a few clicks.

I know for a fact that many small businesses have files such as this one and then create invoices one by one in what can be a very boring but also potentially filled with errors task. First, here is an idea of what my file looks like:

I simply created a sample invoice that you can see here:

It’s not very good looking but the goal here wasn’t exactly that either:) I have the formulas (all vlookups) in yellow just to give you an idea.

What I will do is simply have the macro go to my first sheet, go through each line and write the invoice number in cell C3. When that happens, all other details will be updated.

Then, I will simply have this file copied into a new excel, saved with a custom name. In my case, I will name it:

Pool_cleaning_invoice_1.xls

With one button press, all of these invoices will be created. The possibilities are endless as I could have the macro send out emails, print them, create envelope labels, etc. Here is the code that I used:

Very simple right? As you can imagine, the posssibilties are almost endless. You can download the spreadsheet here!

Whenever friends or acquaintances find out that I blog about personal finance they’re usually curious to learn more. Money is one of those weird topics to discuss around friends.

How do you save money?

How do you get rich?

These sorts of questions usually come up. It’s like when you meet someone that works out. You want to know how to get huge. When you’re confused about money, it’s difficult to know where to start or what to do first. So you just ask general questions. Today I’m going to help you figure out this whole money thing.

What’s the formula for saving money in your 20s?

Increase your skills.

To make any money in this world, you need marketable skills. You have to do something that others are willing to pay for.

How do you increase your skill set?

• Education.
• Experience.
• Online courses.
• Books.
• Private coaching.

Pay yourself first.

Once you start making money, you need to figure out how you’re going to keep as much of it as possible. If you don’t keep any money, then you won’t have anything to show for your hard work. What’s the point of working so hard?

Paying yourself first is actually pretty easy. You go to your employer or your bank and you ask them to take off a set amount from your paycheck every single month.This money comes off your check automatically. You don’t have to do anything. You just fill out the initial forms.

How sweet is that? The money goes away and you never see it. Your goal is to figure out how to live on the rest. This is where the fun begins! You keep on saving this money without even thinking about it. When you’re ready for a big purchase, you simply have the funds available to do so.

Avoid debt like the plague.

We all know that debt is bad. Just like we all know that smoking is horrible for your health and that junk food makes you fat. Yet so many of us are in debt. Why? Because we all happen to be emotional beings.

I don’t want to preach to you. I’ll just leave it at this: do your best to avoid debt. You don’t want to be in your 30s paying off credit card debt that you racked up in your younger days.

Reward yourself.

The trick to remaining consistent with personal finance is rewarding yourself. No fun will lead to you being miserable. I don’t want you to be miserable while saving money. That will make you hate the world of personal finance.

You have to plan excursions, sneak fun into your life, and get out there. I personally try to plan every few months just to reward myself for consistent hard work. How you have fun is totally up to you. As long as you don’t borrow money for a trip, you should be good to go.

That’s the formula for saving lots of money in your 20s.

I did write an introduction to macros in the past which is still very much relevant. That being said, if you use Excel 2007, you might be using slightly different menus so I thought I could go over the menus in order to give a brief introduction to building macros in Excel 2007. First thing to know, you should be looking in the Developer tab that you can see here:

Then, here is what each button will help you do:

–Visual Basic: this will help you read, write and edit code as I described in a previous post
–Macros: If you want to look at the different macros by name to be able to either run them or start editing them, this is the way
–Record macros: In order to create macros by recording them. It’s the easiest way to do it and the perfect way to get started
–Design Mode: Once you select this mode, you will be able to modify buttons without having the macros start running when you touch the buttons
-Insert: If you need to add a button, a form, a dropdown menu or anything else of that nature, this is where you’d go.

Overall, while I’m not a fan of the navigation in Excel 2007 in general, I do still think that for building macros, it’s much better than previous versions. What are your thoughts?

I couldn’t believe that after months of work and struggle, I had finally launched my first product. I was excited to help people and see the response that would come from this huge launch. I had planned everything out well in advance. The work wasn’t easy, but I knew that it would be worth it.

I wrote 6 guest articles. I was all over the net. I promoted the hell out of this eBook. Then the sales page went live. Then it happened.

I got ZERO SALES! I failed at my first product launch. It was sad. It was a complete failure.

This leads to the point of this article…

We all fail.

This isn’t news. Today I wanted to look at dealing with this failure and how you can cope with failure in your career/with any goals you set in your life.

How you can cope with failure from my own experiences…

Allow yourself to fail.

There’s nothing wrong with failing. You have to allow yourself to fail. You have to give yourself permission. The reality is that you’re going to fail and you need to be ready for this. If you’re not failing it’s because you’re not trying hard enough. When you allow yourself to fail, you won’t be as disappointed when things don’t work out the first time around.

Focus on the worst case scenario.

What’s the worst that can happen?

We often realize that the worst case scenario isn’t all that bad. So what if we fail? Do you know how many times I’ve failed? If anyone dare laugh at me, I laugh back even harder since they didn’t try.

The worst case scenario is never as bad as not trying and wondering what could have beeb.

Ditch the excuses.

Stop making excuses. What you tried just didn’t work. Don’t blame the Earth for your problems. Don’t get mad at anyone. You just need to go back to the drawing board and come back with a better plan.

Think of the positives.

There’s always a lesson to be learned from everything. What are the positives that you can take away? From my failures I usually realize what I’ve done wrong. The good news about this is that I can move on and not make the same mistake twice. There’s nothing worse than repeating your mistakes.

Failing isn’t that bad at the end of the day. It happens to all of us. The scary scenario is not trying.

What was the point of this article? It doesn’t matter what your goals are (career, starting a business, weight loss, etc.), we all fail at some point. Too often do we give up when on the verge of something amazing. I don’t want to see any of you give up. It’s not worth it.

“Success is not final, failure is not fatal: it is the courage to continue that counts.” — Winston Churchill

Lately I’ve been getting a few different questions that are awfully similar but I remain unsure of the best title to use or the best way to explain the problem. I’ll get started right away by giving you a few examples that will show you. Hopefully someone can suggest a better title for this post:)

First off, a reader was trying to prepare a spreadsheet that would calculate an amount to bill based on the number of calls using the following grid:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 1 for Next 300 Calls
Rs. 1.5 for Next 400 Calls &
rs 1.75 rest of calls

See the following chart for details:

How would you resolve this? There are obviously many different ways but this is a good example of a situation where using nested if conditions can work out very well. First off, I’ll translate what I’m being told so:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 1 for Next 300 Calls
Rs. 1.5 for Next 400 Calls &
rs 1.75 rest of calls

Becomes:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 0 + (x-500)*1 for next 300 calls
Rs. 300 + (x-800)*1.5 for next 400 calls
rs 900 + (x-1200)*1.75 for rest of calls

This makes it much easier to build a nested if condition. I can simply build it one condition at a time such as:

=IF(D2<500,0,1) =IF(D2<500,0,IF(D2<800)) =IF(D2<500,0,IF(D2<800,(D2-500)*1))) =IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200)))) =IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200,300+1.5*(D2-800))))) =IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200,300+1.5*(D2-800),900+(1.75*(D2-1200))))) You can see the result here and download the spreadsheet here. But before ending this, you might want another example:

Monthly Pay Rate Tax Calculation
$0 – $3000 No Tax
$3001 – $4000 10% (Emoluments-3000)*0.10
$4001 – $10000 15% $100+ ((Emoluments-4000)*0.15)
$10001 – $15000 20% $1000+ ((Emoluments-10000)*0.20)
$15001 – 25% $2000+ ((Emoluments-15000)*0.25)

In this case, the conditions were already set correctly, so I can simply add the “if” conditions one at a time:

=IF(H11<=3000,0,1) =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),"") =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000)) =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000),IF(H11<=15000,1000+0.2*(H11-10000))) =IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000),IF(H11<=15000,1000+0.2*(H11-10000),2000+0.25*(H11-15000))))) You can see the end result here and download the spreadsheet: