Excel Vlookup Lesson

Everyone uses excel to some degree. I'm going to try and teach you how to use vlookup a useful tool for pulling data from one page to another.

We will look at the directions from Microsoft vlookup  and expand on them
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value    The value to search in the first column of the table array. Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
Table_array    Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.
Col_index_num    The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:

• Less than 1, VLOOKUP returns the #VALUE! error value.
• Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup    A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

• If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders.

• If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

So  VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

My quick example with some notes:

=VLOOKUP(B1,Sheet2!A1:H34,3,FALSE)

So we are looking at Sheet2 columns A1:H34 for an exact match to B1.

if we find a match we return column #3 in the table_array which in this case is column C.

if we we don't find an exact match it will return #N/A. if we want it to return another value we can place an If statement before the VLOOKUP

=IF(ISNA(VLOOKUP(B1,Sheet2!A1:H34,3,FALSE)),"",(VLOOKUP(B1,Sheet2!A1:H34,3,FALSE)))
Now what we did here was perform a check to see if the result of the VLOOKUP is NA =IF(ISNA(VLOOKUP(B1,Sheet2!A1:H34,3,FALSE))
 If this statement is true we return the value "" or a blank cell.

if it is false we know there is a value that matches our VLOOKUP and can proceed as normal.

Boost Your Wireless network with DD-WRT

I know alot of people are frustrated with their wireless networks and stumped as to what they can do about it. I'm going to give you some tools that I used to increase my wireless network to over 100 yards of coverage. And that is through walls trees and a workshop with lots of metal and equipment.  I'm using the Linksys by Cisco WRT120N as my base router. (It was not my purchasing decision) It is labeled an a N router but it is not. That's for another time.

This router has an internal antenna and not compatible  with DD-WRT. First thing I did was install a parabolic antenna on my router.  See the Video by TechAnvil.com

That increased my range from 25 feet to 50 feet. Better I know but not quite there yet.

Next I bought a second router Linksys WRT54GL a real work horse of a router.

Installed DD-WRT by following the instructions on the Wiki tutorial.
When you follow the directions make sure you do a 30/30/30 reset before and AFTER you install the firmware. Better but still not enough range for my needs. So I decided to use the WRT54GL as a repeater.  But I still needed a bigger antenna, or Cantenna! Just what I will do build a cantenna

802.11b Homebrew Antenna Shootout - 2/14/2

Hunt's Pasta Sauce Waveguide Cantenna

This wave guide antenna has the following specs: Can: Hunts Pasta/spaghetti Sauce 26.5oz. Can diameter: 3.25" Can length: 5.62" Driven element (copper wire that sticks out of N-connector) length: 1.21" Driven element installed 2.49" from back of can.  So now with these elements combined my network extends over 100 yards and is very strong on both ends

Khan Academy

I'm not sure if many have heard of Khan Academy, I didn't take the time to explore it before today. I learned that Khan will be offering Java as the new language that it will be teaching for basic computer science. Another great thing is they offer video here is something from the current selection on the Introduction to python introduction to programs data types and variables. This with learning Python the hardway are a great way to learn Python and soon to follow Java.

Java

I want to talk to you about Codeacademy.com I signed up with the code year program they are offering for free lessons in Java. I am admittedly a bit behind as I am working on week 3 lessons. So far the lessons are really we designed and the community is great. If  you get stuck in a section just click on their Q&A button and you can ask a question and get a reply or just look and see what others are doing. I'm looking forward to seeing what they will have me completing in the very near future.

Raspberry Pi

The New Raspberry Pi has been released. I heard about it a few monthes ago and have been very excited about it.
The Raspberry Pi foundation is going with a licensed manufacturing setup with RS Components and Premier Farnell signing up to manufacture and distribute the boards. Right now it’s a strictly one-per-customer situation, but within a month or so you’ll be able to order as many as you would like.

Right now the foundation is focusing on the slightly more expensive ‘B’ model Raspberry Pi that includes Ethernet and USB over the $25 base unit. The ‘A’ model was originally slated to come with 128 MB of RAM, but due to some clever cost saving strategies, the team increased the base RAM to  256 MB.

Commentors pointed out that Newark was selling Raspberry Pi's for the US market, but they’re out of stock. The best advice may be to wait a few months for production to catch up to demand.

HP Z1 Workstation Preview

The New HP Z1 the world’s first all-in-one workstation with a
27” (diagonal) display that snaps open to let you swap out parts and make upgrades. No tools required.  (from the HP Site)

So in short the HP Z1 is the first all-in-one that we can upgrade. Want to add a new graphics card? not a problem. The Z1 sports a NVIDIA^® Quadro^® graphics card that is not part of the main board. More RAM? This small computer has 4 ddr3 slots and supports 36GB's of it. How about Web Cam or Speakers the Z1 can handle that as well.

Whats this? According to the HP Site you can get your own Z1 installed with a flavor of Linux!!!


HP Z1

The Oscar Party 2012 edition

This weekend 02/06/12 was the Oscars. To my family its bigger then the Super Bowl. We had 20 people over at my In-laws house for a pot-luck tradition going almost 10 years back. We all make food based on the nominees. Ill try and list some of the dishes and from which movie they belonged. My wife and I made Buffalo wings for "Rio", our closest friends brought their interpretation of the Deathly Hollows, consisting of Doritos, Oreos, and, Pirouette® Chocolate Hazelnut Rolled Wafers. We had fried chicken, Cupcakes featuring Kermit, a pineapple rum cake in honor of "The Descendants". Lots of good food and drinks. 

Now for the real action the main event. We each pay $5.00 to receive our ballots and fill them out with our picks for each of the 24 categories. Then we rank them 1 - 24 points based on our confidence that they will win for a possible total of 300 points. 

There were 18 of us playing and 1 person who shall remain nameless is the usual winner and  the "ringer". We only see him twice a year and he has one the Oscar party pool for 5 or 6 straight years. Like I said "Ringer".
My only goal was to beat him and only him especially since I have never really been in the running. 

Since this being at my in-laws and the first year with this points based system I decided to use the internet to my advantage. I looked up the odds from three different gambling sites (oddschecker.com , bookmaker.net , and lasvegassportsbetting.com ) to compare and make informed decisions for my ballot and assigned category values based on the points spread. 


Well, my strategy paid off and I won, I WON I can't believe it. I'm now $90.00 richer and it feels great. It was a real nail biter towards the end and if I had one all of the last 4 categories I would have lost by six but since I lost "Best Actor" I one. I know confusing. Let me explain, as of the last 4 awards I was in the lead but another person was close. We had the same selections for Best Actor (5points), Best Actress (4 points), Best Picture (23 points) and, Best Director (22 point). My opponant had the same selections but with Best Actor (21 points), Best Actress (20 points), Best Picture (23 points) and, Best Director (22 point). So a big defference in points for the same categories. We both lost best actress we chose Viola Davis and when Meryl Streep won, I have never been so happy to lose a category before. That clinched my victory.