excel formula with example Archives

Mar262020 by excelhelp13No Comments

Vlookup And Countif In Excel

VLOOKUP & COUNTIF -plays a vital role when they both are applied together. Vlookup has limited power. Suppose the lookup value is available more then one time in the specific table or a range then vlookup will pick the the first matching value it found and so as a result you won't get the exact match in some cases or you might be unaware of duplicate value available in a database. Here Countif plays a vital role Countif function check the lookup value in the database and as result it shows how many times that value occurs in a particular database or for a given range. NOTE: The lookup value must be the exact text or numeric number. PARAMETER VLOOKUP: - (Lookup Value, Table Reference, Column Index, 0) 0 - For Exact Match) PARAMETER COUNTIF: - (Range , Criteria) FOR...

Mar172020 by excelhelp13No Comments

IF Condition In Excel

FORMULAS, Logical

IF formula is used for a logical test to give one value for a TRUE result and another for a FALSE result. For example, if the value is less then 50 then average else good. You can use nested IF condition. Example shown below in image FORMULA: =IF(A2>50,"Good","Average")

Mar172020 by excelhelp132 Comments

MATCH Function In Excel

FORMULAS, Lookup & Reference

MATCH function is used to find a specified reference value position in a range of cells, and then returns the position of that reference value. Always remember that match function only take a single column or single row as a lookup array(range). Match parameter ( Lookup Value, Lookup array, 0 (For exact match) ) (1) FOR COLUMN DATA Example given below in image. FORMULA COLUMN: =MATCH(B2,A2:A4,0) (2) FOR ROW DATA Example given below in image. FORMULA ROW: =MATCH(B2,B1:D1,0)

Mar172020 by excelhelp131 Comment

INDEX Function In Excel

FORMULAS, Lookup & Reference

INDEX Function give result the value at a given position in a range or array. Index is used to get single values or entire rows and columns. Index function extract the specified row and column value from a table (database) available in excel. How to use : INDEX(reference, row_num, [column_num], [area_num]) <----(Optional) . reference:- Select the entire database from which you want to get the value. row num:- Then enter the row number from which you want to get the value. column_num : Then enter the column number from which you want to get the value. [area_num]) is (Optional) . Example shown in image below. FORMULA: =INDEX(B1:E5,3,4)

Mar162020 by excelhelp13No Comments

ROW Function In Excel

FORMULAS, Lookup & Reference

ROW function in excel is used to know the row number. It will give the the row number in according to the reference given. Example given below in image and video. FORMULA: =ROW(A2)

Mar162020 by excelhelp13No Comments

RIGHT Function In Excel

FORMULAS, Text

Excel RIGHT function extracts number of characters from the right side to a given cell reference. For example, =RIGHT("Game",3) gives"ame". Example given below in image and video. FORMULA: =RIGHT(B2,3)

Mar162020 by excelhelp132 Comments

LEFT Function In Excel

FORMULAS, Text

LEFT function is used to extract the specific number of letter or a word from a particular cell. It has only two parameter.First cell reference and second number of character. Left function extract from left starting position. For example "Excel help is a great website" and now if someone need to extract only Excel or Excel help then Left function is the best choice to use it. Example shown below in image and video FORMULA; =LEFT(A2,5)