Mikes Tech Reviews

Excel Vlookup Lesson

2012-03-09T10:42:00.000-08:00

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

2012-03-07T08:34:00.000-08:00

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

2012-03-01T11:44:00.000-08:00

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

2012-02-29T12:03:00.000-08:00

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

2012-02-29T10:04:00.000-08:00

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

2012-02-28T10:34:00.000-08:00

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

2012-02-27T09:48:00.000-08:00

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.

Sony Music Boss: Censored YouTube Videos Cost Us Millions

2012-02-24T12:29:00.000-08:00

Sony Music Boss: Censored YouTube Videos Cost Us Millions

Ernesto
February 24, 2012
7
GEMA,
sony
Print

Sony Music’s CEO of international business said in a recent interview that the Internet is a blessing for the music industry. Nevertheless, there are still problems that have to be overcome, such as restrictive copyright enforcement by music rights collecting agencies. The Sony boss says that YouTube revenue running into the millions is being lost because German rights group GEMA’s policies prevent artist videos from being shown online in the country.
For years the music industry has blamed Internet piracy for all their troubles.
Slowly, however, the record labels are starting to realize that the Internet is the future and it will ultimately do more good than harm.
One of the people who embraces this positive view is Edgar Berger, Sony Music’s CEO of international business. In a recent interview he stressed the importance of the Internet, while nothing that the increase in Internet sales almost makes up for the decline in physical sales.
“There is absolutely nothing to complain about. The Internet is a great stroke of luck for the music industry, or better: the Internet is a blessing for us,” Berger said.
“You can not blame the Internet for harmful excesses. On the contrary. It has brought us tremendous new opportunities,” he added.
But with these new opportunities come new rivals from an unexpected corner. According to the Sony boss, music rights collecting agencies are now preventing innovation in certain countries.
In Germany, for example, most YouTube videos by Sony artists are blocked due to the music rights group GEMA, and not because Sony wants it that way. When asked why Sony’s music is not available on YouTube in Germany, Berger responded bitterly.
“It’s not because of us. You must direct this question to the German collecting agency GEMA, they licensed the copyright very restrictively.”
It turns out that the price GEMA want for views is too high for YouTube, and as a result the videos of some of the world’s most famous artists are blocked.

Lady Gaga’s YouTube channel in Germany

According to Berger, GEMA’s restrictive regime is not helping the artists or the music labels. In fact, he says the music industry is losing “millions in revenue”. Additionally, it’s one of the prime reasons why the digital music business is so far behind in Germany.
This is not the first time Berger has lashed out against GEMA’s practices. According to the Sony boss it’s time for the music rights group to embrace the digital age.
“We want to see streaming services like Vevo and Spotify in the German market. [These platforms] must not be blocked by GEMA any longer,” he said earlier. “Artists and music companies are losing sales in the millions.”
So here we have the boss of one of the largest music labels blaming another group for their repressive copyright enforcement. It’s the world upside down, but a promising change from Sony’s side.
This is not the first time GEMA has negatively entered the news this year. Last month the popular music streaming service Grooveshark decided to shut down voluntarily due to the “unreasonably high” licensing costs imposed by the music rights group.

Source http://torrentfreak.com/sony-music-boss-censored-youtube-videos-cost-us-millions-120224/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+Torrentfreak+%28Torrentfreak%29&utm_content=Google+Reader