The other day, I got asked to help out with an excel problem that I initially thought would be very easy to figure out. Basically, here is an example:

Basically, I needed a formula that would determine what needs to be paid for each week knowing that the hourly rate increases over time. By itself, it might be very easy. For example, obviously the first week will be paid $10/hour so $100. For the second week, part would get paid at $10/hour and another at $12. How to determine how many hours to be paid at each quickly became difficult to work with. It was going to work but become very difficult to build. Why? For example, what happens if the worker does 90 hours in his first week? There are many different scenarios, just from those 3.

Using A Binomial Tree

Basically, I decided to take my problem and convert it into a binomial tree as you can see here:

This way, I can build my scenarios first, all of the “nested if conditions” without wondering about all of the calculations. As you will see, it makes things much easier. First, I will build the nested if condition by adding one condition at a time:

=IF(C2<25,1,2) =IF(C2<25,IF(C3<25,1,3),2) =IF(C2<25,IF(C3>25,IF(C3>50,1,2),3),2)
=IF(C2<25,IF(C3>25,IF(C3>50,1,2),3),IF(C2<50,4,6)) =IF(C2<25,IF(C3>25,IF(C3>50,1,2),3),IF(C2<50,IF(C3>50,4,5),6))

As you can see, this part alone can be confusing and difficult to build. Just imagine what it will be once we add the actual calcs! Now, I will replace numbers 1 through 6 by the calcs to get the correct amounts:

For example, the first scenario will be:

(25-C2)*$H$2+25*$H$3+(C3-50)*$H$4

While #2 is:

(25-C2)*$H$2+(C3-25)*$H$3)

#3 would be an easy one:

B3*$H$2

My end result is:

=IF(C2<25,IF(C3>25,IF(C3>50,(25-C2)*$H$2+25*$H$3+(C3-50)*$H$4,(25-C2)*$H$2+(C3-25)*$H$3),B3*$H$2),IF(C2<50,IF(C3>50,(50-$C$2)*H3+(C3-50)*$H$4,B3*$H$3),B3*$H$4))

As you can see, trying to do this from the start would have been difficult. You can also see the result here:

And download the spreadsheet.

I’ve recently been looking into the unknown. I’ve been trying out ideas that have absolutely no guarantees or promises of any nature. In the past, I used to be afraid of this sort of stuff. Now I’m not.

“The unknown is the black void, the place where failure can happen (and so can success). Our instinct, then, particularly if we’re successful at one thing, is to avoid the unknown.” — Seth Godin

Many of my friends are afraid of the unknown. They worry about finding a steady job and receiving a steady paycheck. They don’t want any risks. They don’t want to take any gambles. While I certainly understand this think, it just isn’t for me.

I’ve been working on launching a new site recently, related to fitness. I’ve been putting in tons of hours into this project. I’ve been dedicating lots of time and taking time away from other tasks.

One of my buddies asked an important question:

How much are you going to get paid for this?

My answer was simple: I have no clue!!! I had no answer because I still haven’t seen a penny from this project nor do I plan on ever seeing a penny from it. Seriously.

I actually do tons of work where there are no guarantees. This site could totally flop. I could invest my money and time into something that will never see the light of day or never see any results. That sort of scary to think about. This project could be a flop. I could be wasting my prime. Yet, I don’t mind at all.

Are you afraid of the financial unknowns?

Many of us have perfect reasons for fearing the unknown: family, kids, mortgage, bills, and not wanting to be homeless.

The majority of us, just don’t want to take risks. We want everything to be the same. We resist change. Does this sound like you? Do you avoid financial risks at all costs?

How can you overcome this fear?

Just try out an idea that comes with no promises. Give it a try. Pour your energy and passion into something that drives you and pushes you. You’ll totally forget about the money and everything else.

I also highly suggest that you keep your current job. I don’t believe in quitting. I’m not that insane. You need to have some money coming in. I just believe that you can use your spare time to create something cool. You don’t have to be afraid of failure. I would be more concerned with the same that comes from not trying. Five years from now you’ll have more regret for not trying than for failing.

I’ve failed many times. It stings. You feel sorry for yourself. You don’t want to talk to anyone. You get over it. It’s not the end of the world. I can promise you this.

Are you going to overcome the fear of financial unknowns? Why not try working on a new project for the next 30 days?

I got an interesting email the other day from a reader who was looking to add Boolean logic to spreadsheets. First, you might wonder what Boolean is:

-boolean is a data type with only two possible values: true or false – in excel the two possible values are 0 (false) or 1 (true)

How Boolean Logic Can Be Used In Excel

In some cases, you might want to use it instead of “if” conditions. The example he gave me was:

=(8>3)*(1000/10) which will give 10

Basically, in this case, 8>3 is a Boolean data which is “true” so it equals 1. You’d then end up with a result of 10.

Here is an example. Suppose that you have a sales staff that makes a commission of 5% on all sales on one condition. They must make at least $3,000 worth of sales in that week to make a commission. If they don’t, the commission is $0.

As you can see, this is a perfect case for Boolean. What I’ll do is:

=(D3>3000)*D3*0.05

See the end result here:

There are many different possibilities but as you can see, this makes it very easy to understand and play with. The key is to require a “numeric value” that can be multiplied

Have you ever used or are you considering using Boolean based formulas in excel?

Where have you been putting your money? Most of us want to keep our money somewhere interesting. We search the web to see where we can invest and how to invest. I’m here to give you some help by throwing out some of the most common options.

Where can you keep your hard earned savings?

Your checking account.

Some folks keep their money in their checking account or a fairly-liquid account because they refer to this as their “emergency fund.” While it’s easy to scoff at this idea, it makes sense because you always have access to your money. You don’t have to stress about ever going broke or covering for emergencies. You don’t have to use your credit card to pay for it and then worry about insane interest rates.

What if you want to take some risks?

The stock market.

This is of course one of the more riskier options because there are no guarantees. When you invest in a specific stock, you have to worry about every single move that the company makes. If there’s a public blunder, you can almost always guarantee a drop in stock price. You always have to stay on top of the moves that the company is making. This isn’t passive at all.

On the bright side, the returns can be higher. When the returns are high, you’re smiling and proud of your investment. Just please remember that there are risks involved. Don’t get too greedy. I’ve gotten far too greedy in the past. I once could have cashed out and doubled my money. Instead I waited. Why? Because I wanted more money! What happened? I lost money because I waited too long. You live, you learn.

Real estate.

Real estate is where you need to have the most money to get involved. You need dozens of thousands of dollars before you try to apply for a home mortgage. You need to tie up a serious amount of capital into a property because property isn’t cheap these days.

Since I recently invested into real estate, I’ll share my best tips on the topic:

• Research all properties in advance.
• Save lots of money.
• Perform the property management all on your own.
• Ask for many opinions.
• Shop around for the best mortgage rate.

Safe investments.

This is where a term deposit comes into play. Term deposits are a stress-free investments with flexible options. These are ideal for those of us that don’t want to stress about our money. We work hard enough. We don’t have to risk the chance that we could lose any of our money. That’s the last thing you need.

Where are you going to keep your money? These are some of the top options, but you don’t have to limit yourself to what I covered on here. There are tons of other options.

For reasons that I won’t get into, I had to buy a new laptop and Office this weekend. I hesitated about buying Office 2010 knowing that Office 2013 is perhaps weeks away from being officially launched. That is when the sales guy told me that not only is 2010 offered at a special low price but it comes with a free 1 year upgrade to Office 2013!

How It Works

Once I install and activate my Office 2010 license, I can ask to be notified as soon as the Office 2013 version is ready for download.

Why A 1 Year Upgrade?

From what I understand, the new office version will not really have an upfront cost. Instead, it will charge a monthly fee to use. I personally don’t have much of an issue with it, it will provide more flexibility, make it easier to do upgrades, etc. It’s not as if I was using an Office version for years anyway. I’ll be very curious to see what the monthly fee will be but I’ve heard it would be of $5 or so which sounds very reasonable to me.

So within a day or two, I will be installing Office 2010 and will hopefully get more information about the free upgrade at the same time. For now, I took the “Office Home and Student 2010” version so I’ll also be interested to see if there are significant differences between the two.

Are You Anxious To Upgrade?

Personally, I’m a little worried that changes might be significant in the new version (it did take me a while to adapt from Excel 2003 to Excel 2010) but I do still think that having a cloud-based will offer significant benefits.. especially if all of my macros and advanced function based spreadsheets do end up working