formula in excel Archives

Dec22020 by excelhelp13No Comments

Compare Time Using If Formula

First Select Any Of The Cell For Output Result. Apply The Formula '=IF(TIME(11,17,30)=D2,"Yes","No") Make Sure That Time After 12:00 Hr Is 13:00 As By Default The Time Value Is In 24 Hrs Format. If You Change The Format To 12 Hrs Then It Fine to Have 01;00 Hrs. But Its Vary With Time Formats. As A Result You Can Choose The Output With If Formula. That's It: :)

Nov302020 by excelhelp13No Comments

Rank Function In Excel

Maths, Statistical

Open Excel File.Select The Cell Where you Want To The Result To Get Populated.Then Add This Formula In Particular Cell =RANK(10,A1:A10)There Is Third Optional Parameter (Ascending / Descending ) =RANK(10,A1:A10, 1 'For Ascending Order') or =RANK(10,A1:A10, 0 For Descending Order)Make Sure To Lock The Range By $ Sign.Descending Order Is By Default Set Optional Parameter.That's it :)

Mar162020 by excelhelp131 Comment

BAHTTEXT In Excel

FORMULAS, Text

BAHTTEXT function in excel convert the numbers into text (baht) is one kind of symbolic character. Example shown below in image. FORMULA: =BAHTTEXT(A2)

Mar162020 by excelhelp13No Comments

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)

Mar162020 by excelhelp13No Comments

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)

Mar162020 by excelhelp13No Comments

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 ...

Mar162020 by excelhelp13No Comments

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)

Mar162020 by excelhelp13No Comments

SUMIF Formula In Excel

FORMULAS, Maths

SUMIF function adds all numeric numbers in a given range of cells based on one criteria. Suppose there is name in column A and amount in column B and in column A if a name is repeating more than 1 time and you want to add all the amount It have next to column then Sumif is a best formula to make your work easy. Example as given below. PARAMETER: =SUMIF ( RANGE, CRITERIA, SUMRANGE ) IMPLEMENT: First select the range column, then select or add a criteria, then select the sum range and its done. Example are provided in image and video. FORMULA : =SUMIF(A2:A9,C2,B2:B9)

Mar152020 by excelhelp13No Comments

SUM Formula In Excel

FORMULAS, Maths

SUM function is used to get automatic total of the numbers value in a selected range. Thereafter If you change the value in any of the selected range it will auto calculate and give you the exact final result. Please note that If there are text in some cell and some cell are with numeric value then sum function will ignore the text cells and will give you the result of numeric cells. It makes work very much easy when you have a large database. Example shown in image below. PARAMETER : = SUM ( RANGE ) FORMULA : =SUM(B2:F5)