Connect with us

Tech

VLOOKUP Number – 3 Golden Rules You Can Implement Right Away to Avoid NA Errors

Published

on

If you don’t know these 3 essential rules, it is likely that you will meet some problems when performing a VLOOKUP number in Excel…

  1. VLOOKUP function will show you a #N/A message when the lookup number does not have the same number of digits as the number in the left index column of the table array.
  2. Excel VLOOKUP will return #N/A errors when the number is stored as text in the left index column.
  3. The Excel VLOOKUP function will show you a #N/A message when the number has quotation marks, as you do with texts.

Golden Rule #1 is very important to do a VLOOKUP number effectively in Excel!

Stop the headaches of trying to realize why Excel is retrieving an error. For example, the formula: =VLOOKUP(13270.00,$A$2:$C$6,3,0)… returns #N/A.

You usually are aware that the lookup number 13270.00 is on the left index column.

The fact is, that the error message means the number is not there. You usually insist, yes the number is there because I am seeing it. Big mistake!

The backend left column shows the number 13270.003 as 13270.00. What you see is not what you get, so for Excel all digits count in a number match.

VLOOKUP number Golden Rule #2 – Never have numbers stored as texts in the index column…

Why does Excel store a number as text?

Here are some common reasons: a number with a leading apostrophe, a number with the wrong decimal delimiter, longer numbers after being imported may be turned into texts also.

I will show you an example…

This VLOOKUP: =VLOOKUP(5250,$A$2:$C$6,3,0)… retrieves the error #N/A

The reason is that the left index column has the number 5250 stored as text. You can be aware of this by seeing a green symbol to the left upper corner of the cell. Another good tip to detect numbers stored as texts is that texts are automatically aligned to the left.

So a number aligned mysteriously to the left is likely to be a text. Here is a handy tactic to convert the numbers stored as texts, to numbers, in mass.

  1. Select an empty cell
  2. Copy (basically a zero on the clipboard)
  3. Highlight the cells with the numbers that are stored as text
  4. Paste Special (CTRL+ALT+V)>Add
  5. Press OK

VLOOKUP number Golden Rule #3 – Never write the lookup number with quotation marks…

For example, the formula, =VLOOKUP(“52503,$A$2:$C$6,3,0)… retrieves the message #N/A. I am assuming here that your left index column has numbers (5250 for this case). I refer to this because the formula: =VLOOKUP(“52503,$A$2:$C$6,3,0) will not retrieve #N/A if the left column contains the number 5250 stored as text.

The fact that these small issues force you to debug and have doubts about the function results, means that you will get more productivity once you apply these rules…

You will speed up the VLOOKUP formula writing stage and increase your confidence in the results. This, as I said, will increase your productivity in the end, and it is also my wish that it will more rapidly advance your career and your overall value in your job and profession. Please always enjoy working with your data, and let me know how you are doing. I am here to help, and always glad to do so in any way that I can.

google news

Tech

What is This New Backside Illumination on the New iPhone?

Published

on

By

Recently Apple announced the new iPhone and a number of the hardware changes were targeted at the camera. Thanks to the infamous lost iPhone fiasco we expected the 5 mega-pixel camera and flash, but Jobs announced the camera had backside illumination. That left us puzzled so we did a some research on camera chips.

What is backside illumination?

First stop giggling… OK… I’ll admit I think they could have found a better name. Some of the jokes I saw on Twitter seemed to be especially crude about this feature. Backside illumination is a trick of getting better digital photos by getting more of the available light to where it is measured.

First an overview on digital cameras

The key part of all digital cameras is a chip called the CCD (charge coupled device) which senses the light falling on its surface. The light affects the charge a grid of millions of tiny capacitors created on the silicon when the chip is made (making chips is called fabrication and involves a lot of etching and depositing of thin layers and different materials in precisely controlled ways). For simplicity just think about black and white CCD’s where the array of capacitors corresponds to the array of pixels which make up a digital image (color sometimes uses a lot more tricks). The chip has additional circuitry that measures the charge of all of the capacitors which is basically how the light levels of the image are read. To be able to read all of these capacitors there are minutely thin wires running over the top. They’re not actually wires but a thin layer of aluminum or copper that is effectively sprayed on to the chip and then carefully eaten away using acids to leave connecting traces, which are pretty much metal wires bonded to the chip.

Why backside illumination?

All those wires and other parts of the capacitors sit on top of a square silicon tile (called the die) with the capacitors at the bottom. The circuitry and wires don’t obstruct it too much as it works but it does block some light and scatter some in to surrounding capacitors which reduces the quality of image captured especially with limited lighting. Back to that square silicon tile. Silicon is the main ingredient of regular glass (aka silicon dioxide) but the stuff used in making chips is a super pure silicon crystal and transparent. With backside illumination the issues with traditional CCD’s of the capacitors being on the bottom is quite literally turned upside down and now you shine the image on what was the bottom of the silicon tile and let the light shine through the silicon to the capacitors. This way you avoid all the wires and get more light to the capacitors.

With this new camera chip Apple has shown some beautiful looking pictures but they key performance area will be low lighting. Capturing scenes like the last light of a fading sunset. The earlier iPhones (Original and 3G) had very basic fixed focus 2 mega-pixel cameras which struggled in low lighting. The 3GS brought auto focus and auto white balance that improved performance but still struggled with such scenes, often distorting colors. Now with the new camera chip the iPhone should take vastly improved photos, coupled with the in-built flash to handle low lighting indoors.

google news
Continue Reading

Tech

WOW Golden Pearl Farming – Fast Way to Make Gold on WOW

Published

on

By

Using the Auction House for making gold on WOW is the fastest way to make it on WOW. But what do you do if you need some fast way to make gold on WOW to begin with?

Many players are trying to farm gold as a fast way to make it on WOW – but that is not the best way, it is not even a good way for making gold on WOW at most cases.

Farming is only good at high level and even then it is not the best way!

This is true especially because when you level up you can make more gold at less time – so you will want to start by leveling up as fast as possible before you start farming in the first place.

But there is an exception to that rule – WOW Golden Pearl Farming!

WOW Golden Pearl Farming is good as they can be sold for a lot of gold at the WOW Auction House most of the time.

There are times that Golden Pearl are sold for 4g-5g, but more often than none you can sell them on the WOW Auction House for a very high prices – that is 80g-100g and even up to 200g per pearl!

This means that if you can get 4-6 Golden Pearls an hour from farming you can make between 500g to 1000g per hour, and you can start farming for Golden Pearl from as low level as 40 while leveling in the same time, or go pearl farming as a fast way to make gold on WOW at a low level as 50.

The secret is not to farm for the pearls themselves but to farm for specific clams that drop them as well as buy them from the WOW Auction House itself.

You see there is a secret for farming those pearls, and you will be amazed how simple it is to get them.

google news
Continue Reading

Tech

WoW Best Gold Farming Spots in Wotlk – Wrath of the Lich King

Published

on

By

There is a great need for any player who wishes to survive in the recent expansion to the World of Warcraft (WoW), the Wrath of the Lich King (WotLK). Just like in the real world, living in WoW has become more expensive as new garb and weapons are becoming pricier. Even relaxing at a pub in Dalaran could be worth some amount of gold. This has spurred the emergence of not a few WoW gold guide articles that aim to help the WotLK gamer in farming for gold in Northrend. Most of these articles would tell you where to go to farm WoW gold and the fastest way to make gold in WoW. Even the best WoW gold making guide contains almost the same tips and techniques that you’ll find in these WoW farming for gold articles.

Here are some WoW money making tips that are constantly referred to in these WoW farming guides:

1. Get the right gathering profession. Having the right profession will allow you to be able to build on the right skills to make gold. The best professions are those that are easy to level up and easy to earn gold in. Some of these professions include:

o Mining: Your ores can fetch you lots of gold. If you max your gathering skill level into grand master level, you will be able to mine most ores. You can go to the Burning Steppes and the Un’goro for Mithil and Thorium or the Netherstorm, Terokkar, and Nagrand for Adamantite ore.

o Herbalism: You can find a lot of herbs to sell for gold in the different zones of WoW. You can find some Mana Thistle in the Outlands or some Nightmare Vine drops when you go to Shadowmoon Valley. Other herbs that you can find are Fel Lotus, Ancient Lichen and the Netherbloom.

o Skinning: There is a huge amount of gold that can be made with selling rare leather like those made from cobra scales and wind scales. These can be found at the Shadowmoon Valley, nagrand, and the Blade’s Edge Mountains. You can even be crafty enough to make these into armors and other more useful items to make it worth more gold.

2. Max your crafting profession: Just like your gathering profession, you need to make sure that your crafting profession level is way up before you enter the WotLK. These professions include alchemy, jewelcrafting, and leatherworking. As you would note, these skills can be combined with your gathering professions to be able to increase the gold values of your gathered items.

3. Trade your goods: Watch the auction house. You can make more gold in finding out which ones are selling for less amounts of gold but with potential of selling for higher amounts of gold at a later time. Follow the basic rules of trading which is to buy low and sell high. Watch for things selling at a bargain that could possibly fetch a higher price later on because of an increase in demand.

google news
Continue Reading

Trending