Archive for July, 2011

# How To Use The Excel Match Function

### Excel function tutorials

In our past posts, we have looked at different ways to use reference functions in excel. These are often used in order to help retrieve data in large sets. We have obviously looked at using the Vlookup function but also Sumif, Hlookup, etc. All of these can often be used but in some cases, having access to another function can also be quite helpful. In this case, we are looking at the match function in excel.

What is the Excel Match Function?

It is a function that gives you information about where a specific data point can be found in a database. For example, imagine that you had 10,000 lines in a file and were trying to find a specific data set. There are many different ways to get this done (as is always the case in excel it seems but the match function can give you an easy way to find out where a data lies in the spreadsheet. You can of course use nested functions in order to get additional data.

Suppose that you have a line of data and you need to know where you can find a specific fruit. Here is the data:

I could simply apply the following formula:

=MATCH(“peas”,A:A)

This would return: 10 which indicates that the value is in the line #10. It is not case sensitive (contrary to vlookup) which can also be helpful.

If you wanted to combine a nested function to find the price, we could use this new data:

By using the “offset function”, I can get the price as well:

=OFFSET(A1,MATCH(“peas”,A:A)-1,1)

As you can see, in this case, I could have used the vlookup function as well but it was more practical.

Hopefully this helps! As always, be sure to drop by with any comments or questions regarding this or any other excel function/issue!

# Money Saving Tip: Stop Driving Like a Jerk

### Auto

Do we ever think of the true cost of driving like a jerk?  Rarely do we save much if any time (simple math demonstrates going faster only creates a significant time-savings except during long trips), driving like a jerk generally increases rather than decreases personal stress, and driving like a madman or madwoman also increases the chances of driving mortality or of serious injury.  This post will explore the other ways that driving like a jerk can affect your bottom line.

1) Gas Prices – Driving like a jerk is certainly no way to increase your gas mileage.  Studies have shown that driving consistency is one of the biggest keys to improving overall gas mileage.  Driving like a jerk, however, is often the opposite of consistent driving.  Speeding up to ride someone’s tail, accelerating to shift lanes, and all the rest of the items in the jerk-driver’s toolkit will only end up in the jerk driver paying at the pump.  According to this Wisebread.com article, “By anticipating stops and accelerating moderately, Edmunds.com found that you can get up to 37% in gas savings.”

What the above Edmunds/Wise Bread fact really shows is something that I think most of us know almost preternaturally: that driving in an obnoxious manner hurts your gas mileage and costs you extra money at the pump.

My Unofficial Gas Mileage Test

It’s no true scientific test, but the last few weeks I have been experimenting with gas mileage and driving.  I have particularly been focusing on coasting and trying to maintain a consistent speed.  I pretty much only drive to work, making me a good guinee pig for such a project. Here were my unofficial results:

Week 1: (Full tank of gas and driving as I normally do): Empty tank of gas that I had to refill on my way to work Friday Morning.

Week 2: (Full tank of gas and driving as un-jerklike as I could): 1/4 of a tank left when I got home from work Friday night.

I repeated the test in weeks 3 and 4 and had similar results–except that I had almost 2/5 of a tank left in my “non-jerk” driving week.  (Perhaps because I was getting better at coasting and driving consistently?)

Conclusion: With as expensive as gas is these days, when a jerk cuts me off the first thing I now think is: “karma will get him/her at the pump, no need for a middle finger from me, because paying an extra \$20.00 at the pump every day or two–That’s the ultimate middle finger.

2) Speeding/Traffic Citations – Few people truly grasp just how expensive and annoying municipal/traffic court can be.  For one thing, if you contest the ticket then you still have to pay (in most jurisdictions) for court costs.  These are often times more expensive than the  cost of the ticket itself.

Even more damaging, in many cases, is the fact that points will almost always significantly increase your car insurance expenses.  I know that when I get pulled over, the points are what worry me the most.  If you’re pulling the ultimate jerk driving move (driving drunk), or if you’re otherwise driving recklessly, then you could even face criminal (potentially serious criminal charges) from driving like a jerk.

3) Lawsuits/Damage to Car/Injuries – If you’re driving like a jerk then you’re also increasing your odds of getting into a potentially serious car accident.  Even if there is no injury involved, this will likely increase your insurance rates.  If you have inadequate insurance or decide to pay out of pocket, then that’s an additional upfront cost you wouldn’t otherwise have to deal with.  If you face expensive litigation stemming from an automobile accident, then perhaps significant and life altering expenses may be incurred.

4) Wear on Car – If you have something nice, then taking care of it is the best way to preserve it.  How much sooner does the jerk-driver’s tires wear out?  How much sooner does the jerk-drivers brakes go?  And on and on.

Conclusion

Driving like a jerk is just one added expense after another.

How else does driving like a jerk impact the expenses associated with driving?

Believe me, I’ve driven like a jerk in the past.  I’m sure at times I will drive like a jerk in the future.  But now that I know how expensive it is to be a jerk, I’m going to try and do my best to change my ways.

Besides….nobody likes a jerk.

# How much time will it take to pay back your debt? Using the NPER excel function!

### Excel function tutorials

Over the last few weeks, we have taken the time to look into various uses of some common excel functions. Often, you will not necessarily want to use it in the exact same way but it might give you different ideas and it is often much easier to understand such functions with more concrete examples. In this case, we take a deeper look at the NPER function which calculates how much time will be necessary to either pay back a debt or accumulate a specific amount if circumstances (payments, interest rates, etc) remain constant. It is usually the most realistic way to do things anyway.

In today’s example, we look at a very common scenario. How much time would be required for a consumer to pay back his credit card (or other) debt by paying only a portion of what is owed. Of course, this scenario is often the type of reality test that motivates credit card holders to stay far away from credit card debt. Why? Let’s just say that the 18% or so interest rate makes it very difficult to pay back a large amount.

For the NPER function, what is required is:

-Initial amount: \$25,000
-Interest Rate: 18% annual which we will convert to a monthly rate by doing:

=(1+18%)^(1/12)-1

Then, we will simply compute the NPER function. Take a look at our screenshot:

We used: =ROUND(NPER(C7,C9,-C4,0),1)
Then we changed the result into years= =ROUND(C11/12,1)

It is that easy. For those curious to see it in action, you can download the spreadsheet here. Also, here are the results using a couple of different scenarios:

Paying \$500 per month instead of \$300:

And with a rate of 13%:

# Integrating Multiple “if” Conditions in Excel

One of the recent comments that we received on our “Nested If Statements in Excel with “And” or “Not”, was an interesting example. Here is the question that we received:

I would like to know if it is possible to write a formula to determine “pass” or “fail” given the following criteria:
The scores are given as follows in excel :
example : (subjects are named A to G)
The subjects are typed in left to right in one row, not in columns.

criteria :Subject A must be >= 40, from B to G there must two other subjects >=40 and three additional >=30 – for a student to pass.

In theory, it would be possible to integrate all of that into one single formula. There are however many problems. It would be very difficult and time consuming to build and changing it would be a nightmare. Why not break down the problem down? Here is how I did it:

-Determined if the score for every subject was over or equal to 30
-Determined if the score for every subject was over or equal to 40

Once that was done, I had every information to determine the acceptance or not of each of the three conditions. It looks complex but it’s actually very easy to do and was very quick. Here is the look of my spreadsheet, which you can also download later on:

There are multiple advantages to using this more detailed method in such a case:

1-Easier to understand, we can easily see why failed students did not pass.
2-Easy to modify and adjust over time

# I Write for Others Because I’m An Online Entrepreneurial Failure

Hey everyone, staff writer Chris Thomas of FreelancePF here again.  This time to tell you the truth about why I’m a staff writer for other sites: i’s because I’m an online entrepreneurial failure.  I’ll admit it again: I’m a Failure.

For as long as I can remember, I’ve always wanted to be an entrepreneur.  To have something all my own.  To either succeed or fail on my own terms.  I took a strange path towards “entrepreneurship”, as I didn’t start following my goals until the worst possible time–after seven years of higher education and more than \$100,000 in student loan debt.

Blogging

It all started with a personal finance blog my wife and I started to detail our efforts at paying down our massive student loan debt.  It wasn’t generating any money–but it was ours, and it awoke the somewhat dormant feelings inside me of my entrepreneurial spirit. I learned everything I could about blogging and online business models.  I was desperate for more knowledge, and it seemed like the more I learned the more I realized how little I knew.  With time, I started making some progress.

The problem was, I couldn’t figure out how to monetize the blog.  Even when I started having a decent audience of 4,000-6,000 unique visitors each month (after only a few months of blogging), I couldn’t figure out how to make enough money for it to pay for itself, let alone generate extra cash.

One sad day I had an outstanding heating bill.  It was for \$1,000 and was unexpected as I was a new homeowner.  I didn’t have enough money to pay it off because my money was all tied up in the home purchase.  I decided I had to sell the blog; my one remaining semi-liquid asset (and yes it’s sad if your blog is the most liquid asset you own), and for better or worse, I was lucky to do so and pay off that bill.

But I was also at a crossroads: I no longer had an online presence.  I had put in thousands of hours and only made a minimal one-time gain.  I was a failure.

I then decided to concentrate on FreelancePF, my new online freelance writing and copy business.  As you could see, I was heading further down the rabbit hole of online entrepreneurship, but also further away from a sustainable passive income source. I have been really lucky to have a diverse and loyal group of clients that I really enjoy working with, but I must write everrything myself to make money.

Think about the business model of the really successful online entrepreneurs:  They’re the guys and girls hiring someone like me to do some writing for them.  They’re the guys or girls that have learned how to monetize blogs and websites to make a substantial online income.  Conversely, most of my business has to be done by me and I will only get paid for the work I complete.  Although most of my business is repeat, and that is a great feeling and a point of pride;  still, something felt missing.  I guess the operative word would be scalability.

Despite limited success as an online copywriter/freelance writer, I found myself wanting to try my hand at something larger.  A “go for broke” kind of idea.

It hit my wife and I one day late last Spring.

Website

I had been searching for the perfect Mother’s Day Gift.  Unfortunately, most of the gift suggestion websites I came across had too many ideas.  I might as well have just walked into a mall.  They had all the categories and would even break things down into “personality types,” but then there would be tens, hundreds, or even thousands of options.

My wife and I have a personal philosophy of minimalism.  We believe that simplicity and minimalism are needed today when we are constantly bombarded with too many choices–most of them poor.  I talked over the gift problem with my wife and we saw a need–the need for a gift suggestion website that was different.

We started the project with gusto just a few short months ago.  So far, we’ve earned only \$2.66 off Amazon referrals.  We’re trying to diversify because Amazon is shutting down its referral service in many states.

We still haven’t learned how to properly market the site.  We still haven’t learned how to properly monetize any site–even though the whole purpose of this site is, in many ways, monetization.

More uninspiring still, we have started a personal finance blog that now fails to draw even 1,000 unique visitors.  It feels like we’re moving backwards.  Again, it’s just further proof that I’m an online entrepreneur failure.

Why I’m Telling You About My Failure

I think there’s a few things to learn from my story(s) of failure.

One: always make sure you have a proper emergency fund.  We didn’t and it forced us to sell something quickly that was on an upward swing.  That’s never a strong bargaining position.

Two: Trying different things might be bad.  My entrepreneurial A.D.D., has probably been more of a detriment than a positive. I’m constantly searching for the next brilliant idea and losing hope after a few months of little or no success with my current ideas.

Three: It’s better to be the boss than the employee.  When you can make money from your own sites, then you’re in control.  When you have to search for clients like I do, then it’s not as pure a form of entrepreneurship (in my opinion).  However, it’s the best I can do and actually does generate some extra cash.

Four: Remember that even a failure like me earns around \$500.00 extra a month from my side-job of writing for others.  I could earn a lot more if my day job didn’t require me to keep my jobs to a minimum.  There’s a million different ways to make money online, and if you can find one then you won’t be a total failure.

At the very worst you’ll be getting paid to write about failure like me, and there’s certainly worse things in the world.

Conclusion

Entrepreneurship really is a journey.  It takes lots of baby steps, and sometimes false starts before you get to where you need to be. May you’ll get there, and maybe you won’t.  But you won’t know if you don’t try.

I hope your entrepreneurial journey is life-altering and exhilarating.  And full of enough failures to teach you the road to success.

Best of luck in all your entrepreneurial ventures.  Both online and off.