cheat sheet biology and more essays title s and all, Lecture notes of Biology

Download cheat sheet biology and more essays title s and all and more Lecture notes Biology in PDF only on Docsity! List of Basic & Advance Formulas used in MS Excel IT – 9626 By: Asim Ali Khan 0333-4316006 Name Formula / Function Used for Sum =Sum(b1:b10) To do the addition Min =min(b1:b12) To find the minimum value out of range Max =max(b1:b12) To find the maximum value out of range Integer =INT(F7) INT function returns the integer portion of a number Value =VALUE(F7) VALUE function converts a text value that represents a number to a number. Average =Average(b2:b12) To find the Average of range AverageIF =AVERAGEIF(B3:B8,"HR",D3:D8) AVERAGEIF function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria. AverageIFs =AVERAGEIFS(D3:D8,B3:B8,"HR",C3:C8,">5000") AVERAGEIFS function returns the average (arithmetic mean) of all numbers in a range of cells, based on multiple criteria. Concatenate =concatenate(B2,c2) =b2&” “&C2 =concatenate(b2,”,”,c2) To join two fields data together with a space or a comma between Left =left(a2,1)v er Extract one character from the left most side Right =right(a2,1) Extract the right most character from data Mid =mid(a2,2,1) Extract the second character from the data and display only one character Length =len(a2) Count the number of characters in a2 cell Round =round(a2,2) Round the contents of cell to two decimal places Round Up =ROUNDUP(E7,1) ROUNDUP function returns a number rounded up to a specified number of digits. (Rounds away from 0.) Round Down =ROUNDDOWN(E7,1) ROUNDDOWN function returns a number rounded down to a specified number of digits. (Always rounds towards 0.) ROUNDDOWN behaves like ROUND, except that it always rounds a number down. Naming cell or cell range Select Cell Range and Name it by writing name in Name Box Naming, or providing absolute or relative refrencing List of Basic & Advance Formulas used in MS Excel IT – 9626 By: Asim Ali Khan 0333-4316006 Count =count(a2:a10) Count the range from a2 to a10 Count if =countif(a2:a10,”yes”) Count how many yes are available in range a2 to a10 CountA =countA(a2:a10) Look at to range a2 to a10 the number of cells which are not empty CountBlank =countBlank(a2:a10) Counts how many blanks in the given range CountIFs =COUNTIFS(B3:B8,">10",C3:C8,"Yes") COUNTIFS function counts the number of cells in a range, that meets a single or multiple criteria. Sumif =sumif(b2:b10,”>1000”) Sum if above then 1000 values are available in range b2:b10 and sum the prices Sumif =sumif(b2:b10,”wheat”,c2:c10) Sum if word wheat is available in range b2:b10 and sum the prices from c2:c10 Sumifs =SUMIFS(D3:D8,B3:B8,">4",C3:C8,"yes") SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria. Lookup =LOOKUP(F15,A1:A10,B1:B10) used to search one column of data and find data in the corresponding row. For example, if you are searching a column of employee IDs the LOOKUP function can find, say, employee number 12345 in the ID column. Once it has found the ID 12345 it then can return data from that same row. Vlookup =vlookup(b2,code,2,0) Vertical look up to look up b2 value in code range and displaying second column with exact match Vlookup =vlookup(b2,code,2,1) Vertical look up to look up b2 value in code range and displaying second column with approximate match Hlookup =Hlookup(b2,code,2,0) Horizontal lookup to look up b2 value in code range displaying second row with exact match If function =if(b2>=10,”high price”,”low price”) If statement based on condition, if condition is true high price will be displayed and if condition is false low price will be displayed Nested If function =IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 )) It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement.