excelhelp.in Archives

Mar302023 by excelhelp13No Comments

Multiple IF Condition With IFS Function In Excel

FORMULAS, Logical

IFS function is used when we want to test / check multiple criteria for the same cell value as we have used in the below mentioned example. This condition is also known as Nested IF condition. But The IFS condition make it much easier to use in comparison to Normal Nested IF condition. Note : In IFS function if the result is not TRUE or we can say none of the passed condition/criteria matched it returns #N/A error. Syntax : IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2],[logical_test3, value_if_true3]… ) FORMULA =IFS(B4<35,"FAIL",B4<=40,"D",B4<50,"C",B4<=60,"B",B4>70,"A")

Mar42023 by excelhelp13No Comments

Number Formats In Excel

Home Menu, Menu Bar

Definition:- The Number Format Is A Special Code To Control How Values Are Displayed In Excel. Number Formats Change The Way Numeric Values Are Displayed. But They Do Not Change Actual Values. General - General Option Is The Default Option Applied By Excel For Any Value Number Or Text Format 1. Number:- Number Option Is Used For General Display Of Numbers. The Decimal Number Decimal Can Be Specified. if Want To Display Negative Numbers In Different Formats. 2. Currency:- The Currency Option Can Be Used To Specify Any International Currency Symbol With Numbers. And Also To Specify The Decimal Number. 3. Accounting:- Accounting Option Similar To Currency Option. The Accounting Option Can Set The Currency Symbols And Decimals Together. 4. Date:- With ...

Mar32023 by excelhelp13No Comments

Create Dynamic Dropdown list with Indirect formula

Data, tips & Tricks

A Dynamic dropdown list or dependent dropdown list is very useful while working in Excel. Which makes our work easier and time get saved. Here we have an example of how to create a dynamic dropdown list or dependent dropdown. Example- We have names of some states and their cities. While working in Excel, you should create a dropdown list so that you do not have to type the name of the state or it's city repeatedly. If we select the state name in dropdown the list of cities of that state will appear automatically. We have the following data. In which dropdown list is to be created in column F3 to G6. Steps- To create a dropdown list of state names Select F3 to F6 In the Data tab, Click on Data Validation. In the dialog box that opens, select “L...

Dec222020 by excelhelp13No Comments

ISERROR Function In Excel To Check Error

FORMULAS, More Functions

The ISERROR Function Will Return True If The Reference Cell Or Given Value Is An Error And Will Return False If It Is Not An Error For A Given Cell Reference Or Value. It Works On Errors Are #N/A, #Value!, #Ref!, #Div/0!, #Num!, #Name? And #Null. To Know More About All This ERROR Type Please Visit This Post. ERROR TYPE For Example, Let See The Results From The ISERROR Function When We Provide The Following Data.

Dec222020 by excelhelp13No Comments

ISERR Function In Excel

FORMULAS, More Functions

DEFINITION:- Excel ISERR Function Can Be Used To Check For Error Values, Except #N/A. The ISERR Function Returns True If The Cell Or Any Value Is Any Error Value Except #N/A. This ISERR Function Includes The Error Type Are #Value!, #Ref!, #Div/0!, #Num!, #Name?, And #Null Error. Otherwise It Will Return False. To Know More About All This ERROR Type Please Visit This Post. ERROR TYPE For Example Let See The Results From The Function When We Provide The Following Data. SYNTAX- = ISERR(VALUE)

Dec222020 by excelhelp13No Comments

ISBLANK Function In Excel

FORMULAS, More Functions

DEFINITION:- ISBLANK Function Used To Check Whether A Cell Is Empty Or Not. ISBLANK Function Returns True When A Cell Is Empty And Return False When A Cell Is Not Empty.

Dec222020 by excelhelp13No Comments

Choose Function In Excel

FORMULAS, Lookup & Reference

DEFINTION:- The Excel Choose Function Returns A Value From A List Using A Given Index Number Or A Position. Example 1 =Choose(2,"Car","Bike","Plane") Returns "Bike", As Bike Is On 2Nd Position Listed In The Value Parameter. This Way You Can Assign The Index Number And Thereafter A Value As A Sequence. Example 2 =Choose(C68,"Car","Bike","Plane") Returns "Car" Because The Cell Reference Having A Index number 1 And Car Is On 1st Position Listed In The Value Parameter. This Way You Can Assign The Index Number And Thereafter A Value As A Sequence.

Dec222020 by excelhelp13No Comments

INFO Function To Know Environment Of Excel

FORMULAS, Text

DEFINITION:-The Excel Info Function Returns Information About All The Current Environment In Excel Application Including Platform, Version Of Excel, Number Of Active Worksheets In An Active Workbook And So On. To Use The Info Function In Excel Supply The Type Of Information And Excel Will Provide The Result In Text There Are Seven Types Of Information Available. TypeInformationSyntaxDIRECTORYPath Of The Current Directory.=INFO(“DIRECTORY”)NUMFILENumber Of Active Worksheets.=INFO(“NUMFILE”)ORIGINThe Cell That Is In The Top, Left-Most Cell Visible In The Current Excel Spreadsheet.=INFO(“ORIGIN”)OSVERSIONOperating System Version.=INFO(“OSVERSION”)RECALCReturns The Recalculation Mode - Either Automatic Or Manual=INFO(“RECALC”)RELEASEVersion Of Excel That You Are Running.=INFO(“RELEASE”)SYS...

Dec212020 by excelhelp13No Comments

Sumproduct And LEN Function To Find Characters In A Range

FORMULAS, Maths, Text

SUMPRODUCT Sum The Return Value True And False. True = 1 And False = 0 . LEN Function Check The Len For All Cell And Return The Length As Array. For More Detail Please Check Image Below.

Dec212020 by excelhelp13No Comments

Vlookup And Indirect On Different Range

FORMULAS, Lookup & Reference

Vlookup function is a vertical lookup function which only lookup from left to right for a given range and criteria. =Vlookup (Lookupvalue, table array (range) , Column index number, 0 for exact match or 1 for partial match. ) Indirect function convert a text to a valid reference. =Indirect("A2") in this bracket A2 is stored as text but using with indirect it will convert the A2 into a cell reference and return the value exist in cell(A2). In this example we will first create a Named Range dropdown list For Country And Item Name, Thereafter once we select country and item name from drop down list Vlookup and Indirect function will return price from particular county and respected item list. Please follow the steps shown below. Select the range of item list and price and g...