A few years ago, we had a tutorial about how to build a pivot table, which you can see here. A major advantage of using Pivot Tables is that it’s very easy to make sense of large sets of data and draw summaries.

Last week, I received an email from the Penn State Extension which is working with a database that will help growers determine what pesticides are most effective to use for specific conditions or fruits. She has a huge list populated by the farmers for Apples for example where she is able to see different sets of information, here is a preview (as always, the full file is available at the end of this post):

The first question I was asked was the best way to get the number of times each pesticide had been used. My first answer was a very simple “sumif excel function” which I often use here.

As Sladjana let me know, that would work but it would require:

-Having the list of every single pesticide being used
-Would require updating the list when new ones were being added

Not ideal indeed. A macro could have certainly worked but it seemed unnecessary. I ended up going for a pivot table. How? I simply created it by clicking on the icon seen here:

Then I selected the data (including headers of course!) and was presented with this pivot table:

Then I dragged the column: “Pesticide Trade Name” into both the bottom left section and the bottom right section, which by default gives me the “count” of times it was used, see the result here:

It becomes very easy to update when the data changes, simply right click and select “refresh”

Easy enough? And it gives a complete picture that could become much more detailed depending on what is required/useful!

Feel free to download the spreadsheet. Thanks again to Penn State Extension for providing the data.

The summer is here once again. Are you ready to enjoy yourself? Today I wanted to look at how you can plan a camping trip for summer fun. For those of you living in parts of the world where it’s only warm for a few months out of the year, you’re going to want to enjoy the little bit of sunshine that comes around.

Just this past weekend, I went camping with a group of friends. I haven’t been in a while. It was great to get away from the city life and to get in touch with nature for a few nights.

How can you plan an amazing camping trip?

Book your destination first.

Where do you plan on going? The first step is to figure out what your desired destination is. Do you have anything in mind?

How can you find a good place to go camping?

• Ask around. I usually ask a buddy that travels often about possible destinations.
• Check online. The Internet is highly valuable for finding deals and options on where to visit next.
• Take a risk. Once in a while it’s okay to take a risk by just showing up somewhere.

Find out who’s interested.

Do y0u know who’s all interested? You need to find out who’s willing and able to go. It’s important that you collect money from everyone. It also helps to determine who can bring what.

Make a check list of what you need.

It’s time to think of what you need. It’s crucial that you keep an inventory of what you need. Who’s going to bring the drinks? What about the food?

We had made a list and passed it around through Facebook until everything was crossed off it.

Bring the essentials.

At this point you have to remember to bring the essentials. You need a tent, mattress, flashlight, food, and a few other essentials. Without these you don’t have much of a camping trip at all.

Arrive early.

There’s always someone that’s going to be late. This is why it helps to ensure that everyone is ready and that you guys get there at a decent time. You don’t want to arrive in the dark and fumble your way around while trying to set the tent up.

Have a good time.

The summer is short, so enjoy it. Spray your skin so that the bugs don’t bite too much and forget about real life.

That’s how you can plan your next summer camping trip.

Very recently, a comment on a past post with a question turned out to be a very interesting challenge. Without further wait, here is the question:

“Is it possible to do a “ceasar cipher” of three over letters (input A and output is D)

(A=D, B=E, C=F, D=G, … X=A, Y=B, Z=C)
ABCDEFGHIJKLMNOPQRSTUVWXYZ
DEFGHIJKLMNOPQRSTUVWXYZABC

e.g. I LOVE CARS = L ORVH FDUV
e.g. WKDQN BRX = THANK YOU

Please help me, I am having difficulties in doing this in excel vlookup.”

As is always the case, there are many different ways to get this done. I personally felt it would be easier with vba than using vlookups. Since I would likely not be dealing with hundreds of different messages at once, using simple loops. Here is how I represented the issue:

As you can see, I wanted to be able to both uncode a message as he had asked but also use the same one to code something else. Here is the code that I used for the first part:

“Sub decode()

End Sub”

I did very small modifications for the second part. You can see the end result here:

And as always, feel free to download the spreadsheeet here.

A few weeks ago a buddy picked us up from the bar in his new BMW. He had spent al lot his savings on this car and he was excited to show it off and to display how fast it goes. In his opinion a nice car is clearly worth the money.

Should you spend your money on a nice car?

Driving is very expensive. I avoided driving for the longest time because I wanted to save money. I would take the bus or ask friends for rides. I just didn’t want to bite the bullet for the longest time. Why? There are so many better options to spend your hard earned money on, including:

• Traveling the world.
• Your savings account.
• Starting a business.
• Other expenses.

With that being said, you’ll still eventually face the internal debate in your later-20s or early-30s regarding the idea of buying a nice car. Buying a car is often a necessity depending on where you live. The debate comes down to buying a nice car or not.

What are the pros and cons of buying a nice car? Let’s start off with the benefits:

Prestige.

Depending on your profession, you might want the prestige that goes along with a nice car. If you’re in real estate or a field where you travel and visit clients, you’re going to want the prestige that goes along with a luxury car. You don’t want to show up in a 20 year old car that can barely make it there.

You online live once.

You really only get to live once. You can save all your money or you can live life and enjoy what this world has to offer. For some, a luxury vehicle is the best decision because they want to enjoy it. Priorities vary with income and age as well.

That’s the argument for buying a nice car. What’s the flip side? Why should you save your money?

Cars depreciate in value.

We all know that cars go down in value. In fact, a car will often depreciate as soon as you take it home. If you have to make car payments, you’re putting money into a depreciating asset. Does that make sense?

There are better ways to spend your money.

Once again, you can always find a better investment for your money. You can go back to school, upgrade your skills, or travel more often. I have a buddy that takes the bus to work, but counters this with going on many trips. It all comes down to your personal priorities here.

Should you buy a nice car? What do you think?

I’m sure that many of you have these incredibly complex spreadsheets that become bigger, more complex and more difficult to manage over the years. Those create many issues, one of the main ones being that before making even a small change, you have to figure out what kind of impact this might have. Changing even one sheet can create issues. At times you might also want to change how the file is build without having to change hundreds of links. How would you proceed? I like to use names. It’s quite easy to set up, you can simply go into the following excel menu:

I’ll give you one example. If I have a huge financial spreadsheet with many links towards the CAD/USD rate, that might look like this:

So in hundreds of places I might link to this cell. If I were to change the location of this link, it would force me to update all links. Currently when I add a link in another sheet, it would look something like this:

=Sheet1!B3

What I can do though is to create a name, by clicking on the link mentioned above:

Then, I can simply link to that name:

=CADUSDRate

A major advantage of course is that I could at any point change the location of that rate and instead of changing tons of links, simply go back into “insert/name” and modify the location of that name.

It’s a quick and easy way to make it easier to keep your spreadsheet accurate over long periods of time.

Let me know if you have any questions!