Today I wanted to introduce you to a very simple but also very useful function in excel; Concatenate. First off though, here is a context where I will use it. As is so often the case, there would be other ways to get this done but here I’ll use concatenate. First, here is my current set of data:

I would like to change the data to the following form:

FirstName LastName Country

so for the first name it would become:

Roger Federer SUI

First, I’ll get the 3 informations into 3 columns by using the following:

First name =MID(A2,FIND(“,”,A2)+2,FIND(“(“,A2)-FIND(“,”,A2)-3)
Last name =MID(A2,FIND(” “,A2)+1,FIND(“,”,A2)-FIND(” “,A2)-1)
Country =MID(A2,FIND(“(“,A2)+1,FIND(“)”,A2)-FIND(“(“,A2)-1)

The result becomes:

Then I can simply use the concatenate function to combine them by using:

=CONCATENATE(B2,” “,C2,” “,D2)

I also used the “find” and “mid” function a decent bit, you can find out more info about those in previous posts:) You can download the spreadsheet here.

How much money have you spent on a business? How much capital have you raised?

One topic that has been on my mind lately is the idea of spending money on your business. I always think about this sort of stuff because there are generally two schools of thought on financing a business:

1. It’s okay to spend money. You can expense it and it will be itself off in the long run.
2. You should be as frugal as possible. Start with a small investment and grow from there.

Let’s look at what’s involved here…

What are good ways to spend money on a business?

Some of the best investments could be:

• Attending a conference.
• Hiring quality staff/freelancers.
• Buying new equipment.
• Training.
• Upgrading your skills.
• Legal protection.
• Improved resources.
• Any sort of networking opportunity.
• Consulting services.

What are horrible ways to spend money on a venture?

Hmm time for the flip side. What are some horrible things you shouldn’t spend money on?

• Office space if you don’t need it.
• Staff if you don’t have any extra work to delegate.
• Fancy business cards.
• Expensive dining.

Money spent also depends on the stage…

I’ve noticed that your stage and growth will determine how much money you should spend. A rookie might not want to dive all in with spending because they still haven’t proven that their idea can make any money. When you’re getting started your goal should be to find some paying clients first.

When you’re more involved and are making money, then you might want to take some huge risks.

I remember I used to work at a friend’s dads warehouse. One day the back room received a huge shipment. We asked him what the plan was and he told us that he had taken a huge risk and invested into a shipment of new goods. He wasn’t sure it was going to work, but he knew that this was a calculated risk worth taking.

Those were just some rough thoughts on spending money on a business. I’m interested in hearing from you guys.

How much have you spent on a business in the past? Do you regret spending too much money at some point?

It happens in a few circumstances. I bring data from the internet or some type of document and find myself with a bunch of data that I need to rearrange. Just take a look at this spreadsheet for example. When you look at it, it looks perfectly fine right?

Suppose that you need to change all of the “.TO” to “-CA”, how would you do it? You could use the right function. If the text to replace can be found anywhere in the string, you might need to use the find function as well.

Even easier though is the usage of the substitute function such as follows:

=SUBSTITUTE(TEXT, text to find, text to replace it with)

So in this case that becomes:

=SUBSTITUTE(A2,”.TO”,”-CA”)

You can see the result here:

And download the spreadsheet here

Are you ready to finally get in shape? Is a lack of money preventing you from training? Well, this is the article for you…

I like to cover all sorts of financial experiments on here because I want us to save money together and crush our finances. I also want us to get in shape together. It’s time to use our limited financial resources to look better than ever before.

How can you train at home to save money?

You must first realize that getting in shape isn’t 100% all about working out and training. A majority of the battle relies on your diet and what you put into your system. Before you start to stress about training and different workout regimens, the first goal should be to focus on your diet and what you’re eating.

You can check out this resource on fasting and eating right.

Once you improve your eating, you can focus more on different types of strength training.

What about home workout programs?

We live in an era where home-based workouts are gaining popularity. In the last few years all sorts of random home-based workouts have been sold and marketed like crazy to us. Should you give in to the marketing hype?

It depends on how you follow up to be honest. Buying P90X and letting it sit on your desk next to your “to-do” list is a waste of money. If you pop that dvd in right away and use it 3x per week, well then it’s a pretty decent investment.

How can you ensure that you actually use the dvd?

• Create incentives for yourself.
• Find a workout buddy.
• Give it a test run. Use free material online to see if any of these programs are right for you.

Should you buy any equipment?

Perhaps. I have a few pieces of equipment that make training easier for me. What equipment have I purchased to train from home?

1. Kettlebell.
2. Resistance bands.
3. Two 20lb dumbbells.

You don’t need to buy these tools. They just help with your training.

What are some basic workouts to perform at home?

There are all sorts of workouts worth checking out. I recommend that you read the beginner body weight workout at Nerd Fitness if you’re just getting started. You don’t need much to perform this workout routine.

If that workout doesn’t work you can never go wrong with some basic pushups and going for a walk. When I don’t feel like lifting weights, I grab some music and go for a 30-minute walk. If I don’t have time for the gym, I bust out the pushups at home. All that matters is that you push yourself.

That’s how you can train at home to save money and look like an athlete!

A few weeks ago, I received a question from a reader that was trying to create a list of prices for merchandise that he is selling. Basically, he had a list of items, with their cost, as you can see here:

He wanted to apply the following formula to calculate the prices:

The selling prices will be;

$0 – $10 – cost price only
$10.01 – $20 – cost+(cost*50%)
$20.01-$100 – cost+(cost*40%)
$100.01 – $99999 – cost+(cost*30%)

I also asked him if he wanted prices to be rounded in some way (either up or down, to the nearest dollar, etc). For now, he only needed prices to be rounded to the nearest penny.

This is another example of how a simple problem can become complex if you try to solve it all at once. I can tell you right now that there are dozens of different ways to do this. Today, I will be resolving it using “Nested If” conditions. The correct formula will likely end ip being fair long so I prefer starting by covering one alternative at a time. So in D2, I first try to get the correct price if the cost is under $10:

=IF(C2<=10,C2,1) Obviously, the "1" is simply for my formula to work. That is the part I will edit as I add conditions. Then, I will add one more condition for items that have a cost of $20 or less: =IF(C2<=10,C2,if(C2<=20,C2*1.5,1)) After a few more such steps, I eventually reached: =IF(C2<=10,C2,IF(C2<=20,C2*1.5,IF(C2<=100,C2*1.4,C2*1.3)) From that moment on, I simply needed to round to 2 decimals so: =ROUND(IF(C2<=10,C2,IF(C2<=20,C2*1.5,IF(C2<=100,C2*1.4,C2*1.3))),2) You can see the result here:

And download the spreadsheet here