MS Advance Excel Course

The AVERAGEIF function in Excel is used to find the average of a range of cells that meet a certain condition. Syntax • range: the range of cells to evaluate against the criteria. • criteria: the condition or criteria that must be met. This can be a number, text, date, logical expression or cell reference. • [average_range] (optional): the range of cells that will be used to calculate the average. If omitted, the function will use the same range as the range argument.  

This function calculates the average from a list of numbers. If the cell is blank or contains text, the cell will not be used in the average calculation. If the cell contains zero 0, the cell will be included in the average calculation. Syntax =AVERAGE(Range1,Range2,Range3… through to Range30)    

COUNTIFS is another Excel function that allows you to count the number of cells that meet multiple criteria within a range. This function is useful when you need to count the number of times a particular combination of values or conditions appears in a range of cells. For example, let’s say you have a list of exam scores and you want to count how many students scored above a certain threshold in a particular subject. You can use the COUNTIFS function to count the number of cells that meet both criteria: Syntax =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)   Arguments • criteria_range1: the first range of cells that you want to evaluate. • criteria1: the first condition that must be met for a cell to be counted. • [criteria_range2, criteria2]: additional ranges and conditions that must be met for a cell to be counted. You can include up to 127 ranges and criteria.

COUNTIF is an Excel function that counts the number of cells within a range that meet a specified criteria. This function is useful when you need to count how many times a particular value or condition appears in a range of cells. For example, let’s say you have a list of exam scores and you want to count how many students scored above a certain threshold. You can use the COUNTIF function to count the number of cells that meet that criteria:   Syntax =COUNTIF(range, criteria)   Arguments • range: the range of cells you want to evaluate • criteria: the condition that must be met for a cell to be counted. This can be a number, text, or logical expression.

SUMIFS is another Excel function that allows you to sum up the values in a range of cells based on multiple criteria. This function is useful when you need to sum up values that meet more than one condition. For example, let’s say you have a list of sales figures for different products in different regions, and you want to find out the total sales for a specific product in a specific region. You can use the SUMIFS function to add up only the sales figures for that particular product and region   Syntax =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)   Arguments • sum_range: the range of cells that you want to add up. • criteria_range1: the first range of cells that you want to evaluate. • criteria1: the first condition that must be met for a cell to be included in the sum. • [criteria_range2, criteria2]: additional ranges and conditions that must be met for a cell to be included in the sum. You can include up to 127 ranges and criteria.

SUMIF is an Excel function that adds up the values in a range of cells based on a specific condition. This function is very useful when you need to sum up only those values in a range that meet a certain criteria. For example, let’s say you have a list of sales figures for different products, and you want to find out the total sales for a specific product. You can use the SUMIF function to add up only the sales figures for that particular product: Syntax =SUMIF(range, criteria, [sum_range])   Arguments • range: the range of cells you want to evaluate • criteria: the condition that must be met for a cell to be included in the sum. This can be a number, text, or logical expression. • sum_range (optional): the range of cells that you want to add up. If this argument is omitted, the function will add up the values in the range specified by the ‘range’ argument.  

HLOOKUP stands for ‘Horizontal Lookup’. It is a function in Excel that makes search for a certain value in a row. HLOOKUP allows you to search a data range that is set up Horizontally. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row. Syntex HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) Arguments 1. lookup_value – The value to look up. 2. table_array – The table from which to retrieve data. 3. row_index – The row number from which to retrieve data. 4. range_lookup – [optional] exact match or approximate match. Example 1 Example 2 Example 3

VLOOKUP stands for Vertical Lookup, The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.   The VLOOKUP function is a built-in function in MS Excel, which allows to the user for searches data across columns in vertical data sheet. Syntex =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Arguments Lookup_value: Select the cell where search values will be entered. (The value to search for in the first column of the table.) Table_array: The table range, including all cells in the table. Col_index_num: input the number of the column, counted from the left: Match_Type: Optional. Enter FALSE to find an exact match. Enter TRUE to find an approximate match Note: Both 1 (True) / 0 (False) can be used in Range_lookup.    

The Microsoft Excel LOOKUP function is one of the most popular functions of MS Excel. This Function is categorized under Excel Lookup and Reference functions. Use the LOOKUP function to look up a value in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range.   Some uses of LOOKUP functions are: 1. You can find the exact or appropriate match by using the lookup function. 2. Users can search for data both vertically (columns) and horizontally (rows). 3. It is simpler to use and does not require selecting the entire table. Syntax =LOOKUP(lookup_value, lookup_vector, [result_vector]) Arguments 1. lookup_value – The value to search for. 2. lookup_vector – The one-row, or one-column range to search. 3. result_vector – [optional] The one-row, or one-column range of results.  

The FREQUENCY function in Excel help you to calculating the frequency (no of repeated value) of a data set within a given range of values. It means how many times a data value has occurred amongst a given set of values. Syntax =FREQUENCY(data_array, bins_array)   Data_array – It is an array or reference to a set of certain values whose frequencies we need to count. Bins_array– It is an array or reference of unique data into which you want to group the values in “data array.”   How to apply FREQUENCY function in MS excel ? Step 1 Apply Formula Step 2 Drag All and select cells where formula applied Step 3 Press F2 Key from keyboard Step 4 Press CTRL + SHIFT + ENTER key from keyboard at a time