Tuesday, December 3

FORMULAS

MID Function In Excel

MID Function In Excel

FORMULAS, Text
MID function in excel is text function which is used to find out strings from any mid part of the sentence in excel. Mid function require the cell reference from which the text need to be extract, start number from which character need to be start , and number of character which need to be extract. NOTE: This function count the space too. So in the result it will include a space too. FORMULA: =MID(B2,7,4)
LEFT Function In Excel

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)
SEARCH Function In Excel

SEARCH Function In Excel

FORMULAS, Text
SEARCH function is used to find the position of the word or a character for the given cell reference similar as similar to Find function. Suppose in Cell A3 the text is :- Excel help is the best website and i want to Search that on which position "help" standing then Search function is the best choice to use in place of find function as its not a case sensitive function.. So As a result i will give 7 because help stand on 7th position. Example given below in image. FORMULA: =SEARCH(B2,A2,1)
FIND Function In Excel

FIND Function In Excel

FORMULAS, Text
FIND function find the position of the word or a character for the given cell reference. Find function is a case sensitive. Example given below in image. PARAMETER: = FIND ( FIND TEXT, WITHIN TEXT, START NUMBER) FORMULA : = FIND(B2,A2,1)
CONCATENATE Function In Excel

CONCATENATE Function In Excel

FORMULAS, Text
CONCATENATE function join the two text from different cell. Suppose in cell A2 is Ram and in cell B2 is Laxman and you want to join cell A2 text and Cell B2 text you will use concatenate function of excel. But the concatenate function don't keep a space between two words when it join the text from different cell. So if you want to add a space just add & " " to keep a space between two words. You can join as many text you want from any cells and also custom text by adding it in double quote. Example given below in image, PARAMETER: = CONCATENATE (TEXT 1, TEXT 2 , TEXT 3 ) IMPLEMENT: Concatenate is used to join two text. Note: it join the text without keeping the space so if you want to to put a space between two text please use double quotes after Text 1 = Concatenate (Text...
SUBSTITUTE Function In Excel

SUBSTITUTE Function In Excel

FORMULAS, Text
Excel SUBSTITUTE function is a string function which used to replace a particular word - sentence from a cell. For example :- Excel help is a best website and if you want to replace best with great you will use substitute function in excel. Example given below in image. IMPLEMENT : SUBSTITUTE Function is used when you want to replace any word in an existing sentence or words. First select the cell of existing text then enter the old word that need to change, then enter new word that need to replace and last (instance_num) is optional. Hit enter and its done.
SUMIFS In Excel

SUMIFS In Excel

FORMULAS, Maths
SUMIFS function adds all numeric values in a given range of cells based on more then one criteria. In this function you have to give first sum range ( from which we need to get the total ) then criteria range ( Name column range ) and then criteria1 and then criteria range 2 then criteria 2 (36). Example given in image below. PARAMETER: = SUMIFS ( Sum Range, Criteria Range, Criteria 1, Criteria Range 2, Criteria 2 ) IMPLEMENT: Here the auditor want to know that in this year how many car sold for Maruti Swift and if sold he want a total of the price sold. So the sumifs will take Sum range column (C) and range1 column (A) and 2 criteria 1st (Column D) Criteria range 2 Column (B) and criteria2 Column (E) FORMULA: =SUMIFS(C2:C11,A2:A11,D2,B2:B11,E2) ...