Excel-formulas-and-Functions (1).pdf

USEFUL MICROSOFT EXCEL FORMULAS AND FUNCTION This lesson assumes basic knowledge of Microsoft Excel, focusing on its useful functions like SUM(), SUMIF(), COUNT(), AVERAGE(), IF(), AND(), VLOOKUP(), CONCATENATE(), MAX(), MIN(), Conditional Formatting, Text to Columns, Data Validation, and Graphs. ...

USEFUL MICROSOFT EXCEL FORMULAS AND FUNCTION This lesson assumes basic knowledge of Microsoft Excel, focusing on its useful functions like SUM(), SUMIF(), COUNT(), AVERAGE(), IF(), AND(), VLOOKUP(), CONCATENATE(), MAX(), MIN(), Conditional Formatting, Text to Columns, Data Validation, and Graphs. SUM () FUNCTION The SUM() function sums numerical data in any number of columns or rows, using the format =SUM(sum_range). A range refers to a group or array of cells within a row, column, or combination of both. Sum_range In the example shown in the figure, =SUM(C4:C15) is encoded in the function bar to get the total sales. The range to be added is from C4 to C15 (C4:C15). In simple terms, this action wants to add all the values from C4 to C15. SUMIF() FUNCTION The SUMIF() function sums values of cells within a specified range, while the SUM function only sums values from all cells within the range. The basic format for the formula of SUMIF function is =SUMIF(range, criteria, sum_range). Range -These are the cells that contain values and from which values will be checked against the criteria. Criteria -The criteria/criterion is the value which will define which cells will be summed up. Sum range -This is the range of cells to be summed up. The example in the figure on the right that uses SUMIF function intends to sum up all the sales from C4 to C15 (C4:15) which corresponds to the sales of Manila Branch. As opposed to SUM function which will sum up all values in all the cells within a range, SUMIF function will only sum up the values RANGE of cells which met the criteria specified. Range -The range to be evaluated is A4 to A15 CRITERIA (A4:A15) Criteria -The criterion is the value in A18 which is "Manila" Sum_range -The sum_range is C4 to C15 (C4:C15)  SUM_RANGE RANGE CRITERIA =SUMIF(A4:A15,A18,C4:C15) AVERAGE() FUNCTION The AVERAGE function calculates the average of all numbers in selected cells using the formula =AVERAGE(range to be averaged). It's important to note that the divisor used is the number of cells with inputs, not the number of cells chosen.  COUNT() FUNCTION The COUNT function counts the number of cells with a number value, helping identify missing data or values. The formula format is =COUNT(range to be counted). For example, if a cell has a value of B5, it counts 4, as B7 is not counted. IF()FUNCTION The IF function in Excel allows users to achieve a specific value based on whether a condition is satisfied or not. The formula is =IF(CONDITION, RESULT IF TRUE, RESULT IF FALSE). In this example, if cell (85) is greater than 74.99, the formula will pass and fail. AND() FUNCTION The AND() function in Excel checks if certain conditions are true or false, similar to the IF function. It returns "TRUE" if all conditions are met, and "FALSE" if only one condition is not. The AND() =AND(B5="Passed",C5="Passed",D5="Passed") function can be nested to the IF function for multiple conditions or customized results. In this example, instead of just showing "TRUE" or "FALSE," the nested formula will show "PASSED" if the result of AND function is "TRUE" and will show "FAILED" if the result of AND function is "FALSE". =IF(AND(B5="Passed",C5="Passed",D5="Passed"),"PASSED","FAILED") VLOOKUP() FUNCTION The VLOOKUP function allows users to find data by row in a table or range, such as searching for model numbers or employee names based on IDs, by finding the leftmost column. The basic format for this function is =VLOOKUP(lookup_value, table_range, column index number, range lookup). The parameters mean: Lookup_Value This is the value to be found in the leftmost column of the table array. This can be a cell reference or an actual value. Table_array It is the table from which the data is retrieved. This is the table being searched. Column_Index_num It is the column number in table array from which the value is to be retrieved. The first column in table is Column 1. Range_lookup It is a logical value that can be either "TRUE" or "FALSE." If left empty, the formula will consider it as "TRUE" and will look for the closest value in Column 1. If "FALSE," the formula will look for an exact match. The example uses VLOOKUP function to search for the price of L-1100 phone model instead of manually looking for the price. =VLOOKUP(A4,A10:C16,3,FALSE)  The specification of the sample can be analyzed on the following table: Lookup_value The lookup_value is A4 (or L-1100). You can either enter a cell reference or an actual value, but it is preferable to use cell reference, so that there is no need to change the formula whenever we need to change the value in the lookup_value. Table_array The table array is A10:C16. In this table array, the leftmost column is the "Cellphone Model" column. Column_index_num The column index number is "3" since the formula must retrieve the price and the price column is the 3rd of the table array. Range_lookup The range lookup is "FALSE" to make sure that the formula will look for the exact match of the lookup value CONCATENATE() FUNCTION This function combines text strings from two or more cells into another cell, with a basic format of =CONCATENATE (text1,text2,text3). It can combine up to 255 text strings and 8,192 characters. The text string can be a cell reference or an actual value, enclosed in quotation marks. In the figure, the first name and the last name columns are combined in another column. If a space is needed between the combined string in the combined column, a text string will contain this value "". (space between two quotation marks). In the example, this will force Excel to insert a space between first name and last name. =CONCATENATE(A4,B4)  MAX() AND MIN() FUNCTIONS The MAX function is used to find the highest value in an array, while the MIN function is used to find the lowest value. These functions can be useful in finding the highest or lowest score in an examination. The basic format is =MAX(range) and =MIN(range). In the example in the figure on the left, the range is from cell B5 to cell B9. This is encoded as B5:B9. In =MAX(B5:B9) this example, it intends to get the highest value from cell B5 to cell B9. On the other hand, to get the lowest value, MAX should be changed into MIN.