General

Category for those posts that we have no idea where to categorize them.

How to Analyze Data Using Microsoft Excel

For any profession, the ability to analyze data using Microsoft Excel is essential.

In this post I’ll show you how I was able to look at the Olympic Gold Medal winners (by country), but rank them according to their population or GDP. You’ll be surprised to see the new angle of this.

Now I’ll outline exactly how I did this, because this type of analysis is not beyond the average Excel user.

First I had to accumulate my data sources. Wikipedia is always a good place to start. I did a simple Google search for ‘country medals 2012’, ‘country population’ and ‘country gdp’. Pretty simple, huh? For more difficult searching, check out this article on how to optimize your Google searching. 

I found my 3 lists, with the country names (highlighted in red) being the common denominator across each list. Simply copy paste into Excel, with one list next to the other. This will help me consolidate into a single list later on. This is also why I find Wikipedia useful – because most of their lists follow a similar format and thus you don’t have to tweak the data as much.

 

The next step is to create a single list, although before we get there we need to make sure our common denominator (country) is identical across each list. It’s difficult to tell in this frame, but each country in the Population and GDP list has a space before the country. This simple thing would prevent your lists from consolidating correctly.

To fix it, we’ll use a formula to delete the first character of these cells. Before that, let’s create a bunch of new columns so that we’re all set to build out our analysis.

Notice I expanded the first list to include 2 columns – one for population and one for GDP. Later we’ll use a vlookup formula to pull in the values from the other lists.

To delete the first character in the country cells (for the Population and GDP lists), we’ll use the MID formula, which returns the characters from the middle of a text string, given a starting position and length. We’ll also include the LEN formula, which returns the number of characters in a text string. Enter this in J3:

=MID(L3,2,LEN(L3))

L3 is in the Population list, and it’s the first country listed (” China”). By declaring L3 and then the number 2, we are telling the formula to start in L3, and within L3 to look at the number 2 character. In this case the number 1 character is the blank space, so it will start at “C”. The third part of the MID formula is for the number of characters you want to return (starting at the 2nd character, how many MORE do you want to return). By using LEN, we are telling the formula to return however many characters are actually in the text string ” China”, starting at the 2nd character (“C”). Try this out for the GDP countries as well, and drag the formulas down.

Now that we have a common denominator of country names across all 3 lists, we’ll use vlookup to pull in values for Population and GDP into the first list. Enter this in G3:

=VLOOKUP(B3, $J3:$P244, 4, 0)

What vlookup does is use a lookup value (in this case, B3, which is “United States”) to scan through other lists (Population or GDP lists) and then return a corresponding value (actual population or actual GDP). So what we are asking Excel to do here is look up B3 (“United States”) in the table array $J3:$P244 (entire list for Population). Note here that the first column of your table array (J) needs to be the column that contains your common denominator (country name “United States”). Also note the dollar signs in the array – this is simply to refer to a static table array, rather than shifting the table downwards as you drag your formula down.

Once it finds it (happens to be in J5), we have told the formula to retrieve the 4th column value (which, starting at column J, is returning column M “Population”). The last part of vlookup is rarely used and so I won’t bother to explain. Just put in the number zero. Now, in G3 you should hopefully return this value: 314,168,000. Drag it down and now try to apply the same formula to column H for GDP.

Here’s a snapshot of where we’re at now:

Hopefully you noticed some errors in columns G and H, in the form of “#N/A”. This is common in data analysis, and requires a little manual intervention. If you look at H4, you’ll see this error for the GDP value for China. This is explained by the fact that China is listed as ‘China, People’s Republic’ in the GDP list. Again, this is normal when doing analysis, and there’s no easy way around it for some situations. So what we need to do is filter columns G and H for the “#N/A” values and identify where the issue comes from. Most should be explanatory (another example is G5 and H5 for “Great Britain”, which is listed as “United Kingdom” the other lists).

To further our analysis, we want to compare our 3 lists to one another by using our extended list. We’ll create 4 additional columns.

  1. Gold Medals per Capita
  2. Total Medals per Capita
  3. Price per Gold Medal
  4. Price per Medal

The formulas for these columns will be simple: either Population divided by Gold/Total Medals, or GDP divided by Gold/Total Medals.

Your values should look like this:

If we highlight our country column, and highlight the Gold Medals per Capita column, we can create a helpful scatter plot to illustrate some data analysis. Once you’ve highlighted, go to Insert > Scatter. At this point you should have a graph like the one below, which shows how some countries (ie: Lithuania), although small, performed extremely well considering their actual population. You can create a similar graph for the other columns we created, but for purposes of this post I only explain this one.

To crunch a few numbers, Lithuania secured a gold medal for about every 2 million people who live there. If the USA had that type of efficiency, it’d take home over 150 golds as opposed to 46. China by comparison would take home 637 golds!

Please contact me for specific questions or comments about this.

How to Setup Google Analytics

To setup Google Analytics, you first need your web site up and running. Check our quick guide for this, and also make sure you’ve got the Google Analytics for WordPress plugin installed.

Courtesy of Google Analytics

Also, if you haven’t signed up for a Google Account, do this now. There are too many weapons Google provides you, and they’re all for free and have a plethora of user documentation and tips.

Assuming you’ve logged into the homepage for Google Analytics, click on the Admin tab at the top right of your screen, which should take you to the Account Administration screen.

From here, click ‘+ New Account’ and proceed to enter basic details like the Name (nickname) for your account, and the URL. Once you click ‘Create Account’ you’ll be dropped off on a screen that has your Google Analytics ID (GAID), and some instructions on how to install it. At this point the ‘Tracking Status’ should say ‘Tracking Not Installed‘.

Yoast – our top recommendation for SEO plugins

Our suggestion is to use the Google Analytics for WordPress (by Yoast) plugin for your site to do this. Pull your GAID and then proceed into your WP Admin site and install the plugin if you don’t already have it. Once you install you’ll be asked to ‘select which Analytics profile to track’. Click ‘click here to authenticate with Google‘ and choose the correct email address.

Now you can select a drop down value for the correct profile (this is the Nickname you created in Google Analytics), and the corresponding GAID will populate in the next dropdown.

Or you can select a Manual entry and copy paste the GAID.

It takes up to 24 hours for Google to begin collecting data for your site, and it’s usually quicker. Let the analysis begin!

Top 4 Fitness Video Games

There are those of us who need that extra level of mental stimulation to help motivate us to exercise. Fitness video games can be a cool way to accomplish that. Here’s the SelfwareSource list of the top 4 fitness video games.

Fitness Video Game: Wii Fit Plus (Wii)

Wii Fit Plus, from Nintendo, is a welcome improvement from its predecessor as it features 15 new balance games and 6 new strength training. It allows you to customize your exercise routines to focus on specific body parts; and if you are “too busy”, you have the option of choosing between 20-, 30-, or 40-minute workouts. And just like the original, it uses the same scale-like controller, the Balance Board, to calculate your weight and Body Mass Index (BMI).  Although it doesn’t go beyond yoga and muscle activities, it’s still one of the most effective games in the market today in aiding you to improve your balance and strength. Overall it’s a fun game and an excellent alternative to gyms and fitness clubs.


Price: $55


Fitness Video Game: Your Shape: Fitness Evolved (Xbox 360)

Touted as the most impressive fitness game for the Xbox 360, Your Shape: Fitness Evolved is a controller-free, fitness simulation game that doesn’t disappoint. This interactive fitness product offers an assortment of cardio and muscle routines that are especially designed to suit your fitness requirements. It employs the remarkable Microsoft Kinect Sensor to ‘see’ you clearly by tracking more than 50,000 points on your body. It also calculates your body size and structure by utilizing a hand-motion menu to enter pertinent details like age, weight, and exercise habit. This is to ensure that you don’t exercise beyond what your body can handle. Your Shape Fitness Evolved is easy to follow, and gives you instant and accurate feedback.


Price: $35

Fuelband Rating


Fitness Video Game: EA Sports Active 2 (Xbox 360, PS3, Wii)

With its wireless control system that tracks your movement and monitors your performance, EA Sports Active 2 is a powerful tool designed for people who are serious about getting fit. Featuring arm and leg motion sensors that stay out of the way during training, EA Sports Active 2 has over 70 exercises that help you improve your agility, coordination, and muscle endurance. With a number of activities that target upper- and lower-body, and cardio, it enables you to create a personalized training program to suit your needs. It also monitors your heart rate and has a virtual personal trainer that guides you every step of the way and gives you that extra motivational boost.


Price: $30


Fitness Video Game: The Biggest Loser Ultimate Workout (Xbox 360, Wii)

Whether you are a fan of the television series or not, The Biggest Loser Ultimate Workout is an exciting exercise simulation game that is sure to kick you off the couch. Making an appearance in two of these interactive and dynamic games are the show’s hardcore trainers, Jillian Michaels and Bob Harper. The product features more than 125 exercises in various categories such as upper body, lower body, core, cardio, and yoga. And just like the show, there are no mini games here for casual challenges – only serious workouts. Also, the Xbox 360 with Kinect version offers a number of exciting features to optimize your workout experience like video diaries and a supportive network of online workout friends.


Price: $15


Staying fit is a commitment. But thanks to these fitness video games, you can now break a sweat in a fun, convenient, and effective way (without having to go to the meet market…I mean gym).

Best Fitness Trackers

Baby Pushups

Lloyd at ~2 years

One of the most annoying things about medical technology is how good it’s getting. It seems every day some new piece of research done with state-of-the-art technology tells us exactly how unhealthy we are. To no surprise, we have now been a putting quite a premium on our health and fitness. Here at SelfwareSource, we’re a big believer in using technology to help better yourself. Should you find a use for a pedometer or accelerometer, the following is our review of the best fitness trackers on the market.

Fuelband: Best Fitness Tracker


Nike+ FuelBand could just be the right gadget to kick you off the couch. This pedometer doesn’t just track calories, steps, and time; it uses motivational tactics to get you moving by measuring Nike Fuel – Nike’s measurement of activity. Set your goal and the FuelBand will determine your progress. It features a row of LEDs that changes from red to green as you get closer to the target, making it a pretty effective motivational device.

The beauty of FuelBand lies in its simplicity. It features single button scrolling with two rows of LEDs – a larger one for the readouts and a small one for the Nike Fuel tracker. Due to its circular design that snugly clings on to your wrist, it stays out of the way when you’re breaking a sweat. And it’s water resistant too! It has Bluetooth capability, and on one end of the device hides a USB connector enabling you to sync data to a computer to view your peaks and valleys. Its $149 price tag may be hefty for some, but if you need that extra motivational boost, the Nike+ FuelBand is your ideal tracker.

Technology: Three axis accelerometer or “oxygen kinetics
Price: $200

Fuelband Rating



Fitbit: Best Fitness Tracker


Fitbit Ultra is the smallest fitness tracker in the market today. Don’t let the size fool you though; it packs a lot of punch inside its compact, easy to carry exterior. Fitbit is a sophisticated gadget that effortlessly tracks steps taken, calories burned, distance travelled, and even stairs climbed! On top of that, it also tracks sleep, which is one of the most important factors in fitness.

This easy-to-wear gadget comes with a small base station that connects to a PC or MAC thru a USB cable. Or you can go wireless in transferring your data. Aside from having Android and iOS mobile apps that allow you to log workouts and meals, it also features engaging Web-based analytical tools in tracking your progress. One can log on Fitbit.com and benefit from a supportive community of friends or join public groups to broaden your knowledge on fitness. At a $99.95 price, Fitbit definitely ranks up there!

Technology: Accelerometer
Price: ~$100
 



Xtrail: Best Fitness Tracker

Turn your iPhone into your instant fitness manager that’s as easy as ready-set-go. Xtrail is a no-fuss GPS-enabled fitness tracker. Tap, tap, and you’re off. And you don’t need to buy a separate device, all you need to do is download the Xtrail app and you’re good to go.

Especially designed for runners and cyclists, Xtrail accurately measures the distance, speed, time, pace, calories, altitude, and climb, which is then displayed in clean, configurable widgets for easy viewing. Xtrail also features a detailed map and an interactive graph for more convenient performance analysis. And if you must brag, you can share it in Facebook, Twitter, or email via the popular GPX file format. Plus, this intuitive app retails at $4.99, which is a fantastic deal for the things it can do.

Technology: GPS
Price: $4.99
 



MOTOACTV: Best Fitness Tracker


To all the dedicated athletes and genuine gym rats who have been looking for the ultimate exercise companion, MOTOACTV is perfect for you. Featuring a very macho design of metal construction in all-black scheme with its water resistant and scratch-proof postage-stamp touch-screen, this gadget is in a league of its own. With 40 different kinds of exercise methods such as Running, Walking, Cycling, Elliptical, and Step Machine, as well as more exotic ones like Gymnastics, Snow Boarding, and even Yard Work, the MOTOACTV can handle any fitness task you throw at it.

The internal accelerometer of the MOTOACTV functions as its pedometer. Aside from being Android-powered, it utilizes GPS, Bluetooth, music and online analytics to optimize the workout experience. You can opt between WiFi and cellular connection in uploading your workout stats to its web portal to see how far you’ve come. Plus, it can even suggest playlist to further rev you up. And with $149.99, you’ll have a cutting-edge fitness tracker, a handy music device, and an Android powered watch all rolled into one. It’s like having your own personal trainer and DJ!

Technology: GPS
Price: ~$140



So whether or not you think that a fitness tracker is for you, these are our pics for the best fitness tracker. Overall, Nike Fuelband wins for us. But again, this technology is very new. One this is for sure though, fitness trackers are on the rise. The tech behind these gadgets will only get better, and you can bet Melvin and Lloyd will be reviewing them right here @Selfwaresource with little to no actual exercise done.

How to Use LinkedIn to Find a Job

Job searching sucks and social networking sites occupy lots of your time. So why not occupy your time with advancing your professional career? Here are some rough guidelines for how Generation Y can use technology to find a job.

Complete Your Profile

Spiff up your profile without going overboard. Try to obtain a few recommendations from past or current supervisors to add some credibility. Take LinkedIn’s advice and complete as much of your profile as needed, including ‘Skills & Expertise’, ‘Summary’ and ‘Specialties’. These sections not only fill in the blanks of your profile, they provide companies a means of finding you based off your self-tagged attributes. I also highly recommend adding a ‘Personal Website’ that links to your own personal resume website.

Find the Companies You Want to Work For

This is obviously easier said than done. Hopefully you have an idea of where you’d like to work, and can narrow it down to a few companies. Even if you don’t, you can utilize the Search functionality and target specific people, companies or groups. Since most groups allow you to peruse their public profiles, it’s an easy way to start searching generically and then zoom in on particular groups that may interest you. From there you can get an idea of other groups they belong to, their company, their connections, etc.

Penetrate Their Network

No, not Hugh Jackman in Swordfish style. You need to make connections with people in or around the company you’re targeting. I suggest getting yourself invited into some groups, and look for ways to make meaningful contributions to the right people. There’s a business relationship already. Additionally, you can directly contact someone from the company. However, this doesn’t mean going after the VP of Sales or the Director of Human Resources. Find someone near the level you’d come in at. Tell them you’re interested in the type of work they do, and ask them if they’d be willing to discuss it.

Be Passionately Persistent

Derek Sivers explains how to get hired using the same general principle of targeting one (or a few) companies and then opening up channels of communication, regardless of whether or not they are hiring. With LinkedIn most of the hard work (searching and perusing) is neatly organized for you. You simply need to expand your professional online presence in a methodical way, and eventually you’ll find the open door you were looking for. Heck, you don’t even need to get off the couch to do it.

 

Get a Blog and Get Hired: How To Create Your Own Website Resume

Besides ‘get a job’, the main purpose of a resume is to have it represent the best of you.

What’s the best way to showcase that?

Resume: sure…in 1995

Cover Letter: sure…in 1965 

Facebook: ehhh, little worried about the underage drinking photos and that mysterious water pipe you tagged as ‘The Little Giant

LinkedIn: moving in the right direction, just as long as the companies you’re pursuing have a LinkedIn presence

What can combine these 4 options, and do so in a creative, unique and memorable way?

Your own website resume.

All you need is $50 and a few hours before you’ve got something to be proud of. Here’s a step by step.

Pick a web hosting provider

Someone needs to support the website you will eventually build from an infrastructure perspective, and you don’t want that person to be you. Search for deals, you’ll likely find something under $6 per month, especially if you’re willing to purchase a plan for more than 1 year.

Recommendations: I use JustHost and InMotion

Pick your domain.

This is your www.domain.com

Advice: Choose something simple, perhaps just your name or a small variation on it. Think long term, something your kids or grandkids will refer to if they want to understand your life. Stay away from the 1990’s AIM screen names or anything that will be stupid over time

Recommendations: I use GoDaddy

Point your domain to your web host

Most domain management companies (ie: GoDaddy) will prompt you for your server information before you complete your order and checkout. Some companies provide all in one hosting and domain management. Or you can choose to have separate companies for hosting and domain management.

Install WordPress

Find a theme. You can search and try out thousands of free themes.

I used zeeBizCard for my website resume. It’s got a myriad of social icons that can direct traffic to your various online presences.

Setup Google Analytics

Hopefully you have a gmail address. If not, get one and then sign up for Google Analytics. It’s relatively intuitive, so once you get your site added they’ll spit you a Google Analytics ID. Take that and paste it into one of the critical quickstart WP plugins and then you’ll have some data to play around with once people start visiting your site.

Beef up your SEO

Using your same Google login, you can also create a Google Adwords account. Adwords let’s you pay for your site’s advertisements to pop up all over relevant Google searches. You can specify locations you want to target, budget per day to spend on your ads, or even congressional districts to pinpoint.

 You shouldn’t engage in proactive ads until you’ve really filled out your site and already have a reasonable flow of visitors. Otherwise Google will ‘devalue’ you if you’re trying to throw ads up before people have started visiting your website resume.

Link to your site through social media, particularly LinkedIn where you’ll stand out more by having a Personal Website. Keep yourself in the news feeds by making small updates here and there, and as always keep expanding that network.


 

Quickstart Plugin Guide for WordPress

Assuming you’ve got your web host and your domain set up, it’s time to install wordpress and get rolling with essential WordPress plugins. If you haven’t gotten your web host or domain yet, check out this post for an overview.

This are the bare-bones, most efficient way to get up and running with your blog with the most essential WordPress plugins.

1. Yoast WordPress SEO

Probably the best feature of Yoast WordPress SEO is that you can import all of your titles, descriptions, and keywords from some of the other popular SEO plugins. Very visual in it’s layout, it makes a great beginners–>advanced SEO tool for WordPress.

2. Akismet

Get yourself a configuration key and activate it. This plugin blocks spam and is the universal choice of most WP’ers, mainly since it’s built in with current versions of WP.

3. All In One Favicon

A favicon is the little icon you see in your web browsers tab. This plugin lets you upload any size picture and in multiple formats (ICO, PNG, GIF). Another fun feature is that you can select a separate favicon for the backend part of your website too, so you don’t always have the WordPress icon on the site you spend most of your time on.

4. Simple Contact Form

It is what it is. Throws a small contact form wherever you want (pages, posts, homepage, etc…) and it’ll shoot you an email when someone has contacted you. Safer than throwing an email address on your site, and much friendlier than comments.

5. Twitter Tools 

Integrate your blog with Twitter and vice versa. Quick and easy ways to configure content dissemination.

These are the first 5 essential wordpress plugins I download/activate that help me suit up and look presentable. Hopefully this list will grow as we learn more.

Tim Ferris has a more in-depth look at WP plugins, which may be useful once your site is more legitimized and optimized. Check here for his article.

Please submit any recommendations by commenting below, contacting us or following us on the tweet machine.

1 2 3 4  Scroll to top