Questromworld.bu.edu



Intermediate Excel Tech WorkshopFall 2015IF/THEN StatementsIF/Then statements evaluate a designated cell and then return a true or false value based on the results of that test. They take the form of:IF(logical_test,value_if_true,value_if_false)When using a simple IF:Begin at the cell where you want to outputSelect the cell, value and operator to test such as IF(C3=”yes”Enter the value to ouput if trueEnter the value to output if falseWhen ANDing two IFs:Enter the logical_test in the form: (AND(C3="yes",D3="yes") When ORing two Ifs:Enter the logical_test in the form (OR(C3=”yes”,D3 = “yes”)VLOOKUPVLOOKUP is used to search through tables and return the appropriate values. It takes the form of:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])When using a simple VLOOKUP you have to:Enter the function into the cell where you want the outputEnter the unique identifier to be looked upDefine the table to be searchedDefine which column the data to be returned is located in the tableIf the first column of the data table is sorted leave the last argument blank or enter TRUE, if it is not then enter FALSE.Note that for approximate values always set TRUE and for exact values always set FALSESorts and FiltersIn order to get our data into a useful form, we sometimes need to sort it to different indices, or filter out based on some criteria.To do this we can use Excel’s built in Sort function on the Data Tab. And it will give us the option to exclude out headers, and which column to sort in which direction. It is crucial that all data that is related is selected before being sorted otherwise it will become scrambled.To filter data, use the Filter function on the Data Tab to check off which values for each column are unwanted and then every row that contains that value will be hidden. It’s important to note that most formulas won’t respect these filters but you can copy and paste the filtered date to avoid this.VLOOKUPVLOOKUP is used to search through tables and return the appropriate values. It takes the form of:VLOOKUP(lookup value, table array, col index num, [range lookup])When using a simple VLOOKUP you have to:Enter the function into the cell where you want the outputEnter the unique identifier to be lookup upDefine the table to be searchedDefine which column the data to be returned is located in the tableIf the first column of the data table is sorted, leave the last argument blank or enter TRUE, if it is not then enter FALSE.Note that for approximate values always set TRUE and for exact values always set FALSESide Note: Absolute ReferencesAdding a $ before a cell reference will prevent excel from auto-adjusting the references as they are moved around the sheet.Index/MatchSimilar results can be achieved with the conjunction of the Index and Match functions which take the form:=INDEX(array, (MATCH(lookup_value, lookup_array, [match_type]))Which can be interpretated as:=INDEX(column to return a value from, (MATCH(lookup value, column to lookup against, 0))Some benefits of this include the ability to look left and right, allows columns to be inserted and deleted in the array without breaking, and higher processing speed over large datasets.Data ValidationExcel is capable of checking that cells contain the proper type of data using Validation Rules. This is useful for workbooks that you share with others so they can manually input data. Using the data validation function on the Data Tab, we can set up any rules we want regarding the type and values that are acceptable.String ManipulationSometimes you might need to manipulate strings and other sequences. Some formulas that can help you are:=LEN(text) returns the length of a string=RIGHT(text, [number of characters]) returns a substring of the specified length starting from the rightmost character of the target string=LEFT(text, [number of characters]) returns a substring of the specified length starting from the leftmost character of the target string=MID(text, start position, number of characters) returns a substring of a specified length starting from the specified start position (first character is position 1)=CONCATENATE(text1, text2…) joins two or more strings together=TRIM(text) removes extra spaces at the beginning or end of a string ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download