Well these functions simplify my job at any rate. I’m always asked to explain these functions as they are real time savers.
Vlookup, If Function, Convert text numbers to real numbers, Pivot Tables, Time Colon
Vlookup is extremely useful for looking for a certain number in a column of data and returning data that is associated with that number from a different column. Example: you have a tag number that you have recovered in a fishery and you want to know what DAY it was tagged and what SEX the fish was. The information you’re looking up can be within the same workbook or in an entirely different workbook. The function is written:
=vlookup(Lookup-value,Table-array,Column-number,Range-lookup) Now I will break each one down into something easier to understand.
=vlookup( )
OK this is an easy one. This is your function and it tells Excel what you want it to do, in this case vlookup means vertical lookup and will look up and down a column until it finds a match to the Lookup-value in the Table-array. Everything in the brackets is what where and how you want the data looked up.
Lookup-value
Lookup-value is the cell number (e.g. A1) that you want Excel to find in the Table-array. The Lookup-value must also be in the first column of the Table-array or it will not work. This will be explained better in the Column-number section.
Table-array
OK this sounds confusing but it’s really not. All a Table-array really is, is the data set that you want Excel to look in to find what you chose as the Lookup-value. The Table-array doesn’t have to start in Column A, or be a complete data set. It can be just a portion of the data. but you have to make sure that all the info you want matched and returned is to the right of the Lookup-value(first column) in the Table-array. This will be explained why in Column-number. This data set can be in the same worksheet, a different worksheet in the same workbook or even a different workbook.
If you are copying the formula down to look up a series of cells you will want to lock the Tabble Array range from changing cells. This can be done by placing a $ in front of the row and columns range. The dollar signs mean do not let this value change if you copy the formula to adjacent cells. So the table array range of A1:D10 would become $A$1:$D$10.
Column-number
Now it gets kind of tricky. The Column-number is the number of the column of the from which the matching value ( e.g. Date Tagged) must be returned. So if your Lookup-value(first column) was in Column A, Column B would be 2, column C would be 3 and so on and so on .. If your Lookup-value(first column) was in Column D, Column E would be 2, Column F would be 3 and so on and so on. However the function will not return matches in columns to the left of the Table-array So you must make sure that all info you want returned is to the right of your data . In other words you can’t put -2 (negative 2) to return a match to the left of the Lookup-value (first column) in the Table-array.
Range-lookup
Range-lookup is a logical value telling Excel whether you want to return an exact match or the closest match it could find. If you type TRUE or omit typing anything then an approximate match is returned. If you type False then it gives an exact match or returns #NA if a match cannot be found. If you use TRUE then the data must be sorted in ascending order (e.g. 1,2,3) or an incorrect match may be returned. If you use FALSE the data does not need to be sorted however it is only going to return the first instance it finds of your Lookup-value. If you have more than one instance of that Lookup-value in your Table Array then you will have to find a different way to look it up.
So =vlookup(A1,$B$1:$D$10,2,False) would say lookup cell A1 in the 2nd column of table array of B1 to D10 and only return an exact match.
This is an excellent function for double-checking work, making sure data sets match up or automatically entering size classes from length measurements and so on. The Function is written as:
=if(logical-test,”value-if-true“,”value-if-false“)
=if( )
This is your IF Function. It returns one value-if-true, another value-if-false depending on the logical-test you specify. This is function is useful when you nest it within itself.
Logical-test
a logical-test is any value or expression that can be equated to true or false. Some examples of this are : A1=A10 or A1>A10 or A1<=A10 or A1+A10=B30 or any combo of such that you can multiply, divide, add or subtract. it just has to equal true or false in the end.
value-if-true
value-if-true are the words that are displayed if the logical-test is TRUE. The words can be anything you they don’t have to be “TRUE” just remember to put quotations around them. The value-if-true can also be another formula.
value-if-false
value-if-false are the words returned if the logical-test is FALSE. The words can be anything, they don’t have to be “FALSE”. Remember to put quotations around them. The value-if-false can also be another formula.
So if I wanted to make sure that the Scale books from the lab( A1) match the data from the field(B1) then I would write the function like this:
=if(A1=B1,”match”,”no match”)
Now say I want to return a size class (S,M,L) from a length recorded in the field data. S<399, M<649 and L>650 are the size classes. This involves writing several IF functions within each other. Sounds complicated but is really easy.
=if(A1<399,”S”,IF(A1<649,”M”,IF(A1>650,”L”)))
Remember to watch your brackets for every open bracket you must have a closing bracket. There are also no spaces after the commas.
Convert Text Numbers to Real Numbers
OK have you ever had that problem where you run a formula and the #NA error comes up yet everything looks right. What might have happened is that the the numbers are text and not real numbers. They look like numbers but are not in Excels eyes. One quick way to tell is if the numbers are left aligned in the cell. If they are then Excel thinks they are Text. You cannot reformat the cells to number format unless you double click each individual cell but you can do this.
Got to DATA then click on TEXT TO COLUMNS then click on NEXT, then NEXT again and then FINISH. This will solve your problems 99% of the time. Sometimes you will have to select the column and format it to numbers and this step will work now. And hey I just found out that Excel 2000 doesn’t care if they’re text or real numbers it will still add them up.
Have you ever had a data set that you had entered and you needed to get a condensed version of what it all means? For example, you have just finished entering a set of ages from scales that you have aged and now you want to find out how many are in each different age class. This is a really simple thing to do with a PIVOT TABLE. This can be found by clicking on DATA then clicking PIVOT TABLES. A Window will pop up with some choices for you to choose, choose PIVOT TABLE again. Next select the range of data that you want to make a pivot table from. You must make sure that you select the column headers ( eg length, age, scale book #….) otherwise this will not make much sense. The next screen That comes up is the LAYOUT SCREEN( in Office 2000 the output screen comes up and you must select LAYOUT from the buttons on the bottom). On the right half of the screen are the headers from the data you selected and on the left half of the screen is an outline of the table you want to create. All you do is drag the headers into the table outline for how you want the table to look. So if you want a count of age classes you would take the age header and drag it into the data section of the table. Make sure it says COUNT OF AGES in the header block as it will do SUM and STD DEV as well as many other things. If you want to change to a SUM or STD DEV just double click on the header and many options will pop up. just choose the one you want. Next drag the AGES header into the ROW section and this will break it up by the various age classes. You can have more than one header in the ROW section and you can have more than one action (eg SUM or COUNT or STD DEV) performed in the DATA section. All you do is drag the header in their again choose what option you want by double clicking the header. This will help further break down the data. For example say you wanted to have a table that counted age classes and seperated them by Stat Week Recovered. You would drag the SW Recovered header into the ROW Section The Age header into the ROW section again and the Age header into the DATA section and you are done. You can arrange the headers in different orders by just dragging them to where you want ( because the order matters in the way your table will proccess data). The COLUMN section is used just like the ROW Section. Now you click NEXT choose where you want the table to go and click FINISH. If you don’t like the way the table looks (or you made a mistake) all you have to do is RIGHT-Click on the pivot table you created, select WIZARD and redo the layout.
All I can say is play around with the Pivot Tables and check out the various options within them as they can save you alot of time and once you know how to use them you will use them all the time.
This is not a function but a macro and is very useful when you have to enter row after row (after row..) of time entries and you get really tired of having to enter SHIFT+colon all the time. With this macro all you have to do is enter the numbers and it will enter the colons for you. Here’s how to do this wonderful thing. First of all click here and a separate window will pop up with the code you need to copy. So go ahead and copy it. Now if you already haven’t got your Excel sheet open go ahead and do that now. Next open your Visual Basic Editor. This can be found by going to TOOLS then click on MACROS then VISUAL BASIC EDITOR.
Along the left of the screen you will see your worksheets(1). Double click the sheet that you want the time colon macro to be in ( eg Sheet 1) and another window will open (2). Now paste the code that you copied off my web page into this window.
One thing you will have to be aware of is that this code will only work in a specific Range of columns and rows that you select. The line of code that controls this is the “A1:D800” and I have highlighted it in red in the window that opened (it’s about 4 lines down) In this case my time colon macro will work in column A down to row 800 and across to column D down to row 800. in column A row 801 ( and beyond) the macro will no longer work. Likewise in Column E (and beyond) the macro will no longer work. You can change this Range to anything you want though, so if you know that the times you want to enter are in column D through G then adjust the Range accordingly (eg “D:G” or “D1:G800”) If you do not include numbers in the Range then it will apply the Macro to the whole column.
Now all you do is save what you’ve done and start entering Data. if you want to do this on another sheet just repeat the steps above and make sure you correct the Range.