Many formulas you create use available worksheet functions. These functions enable you to greatly enhance the power of your formulas and perform calculations that are difficult if you use only the operators. For example, you can use the LOG or SIN function to calculate the Logarithm or Sin ratio. You can’t do this complicated calculation by using the mathematical operators alone.

Using Functions

When you type = sign and then type any alphabet you will see the searched functions as below.

Suppose you need to determine the largest value in a range. A formula can’t tell you the answer without using a function. We will use formula that uses the MAX function to return the largest value in the range B3:B8 as =MAX(A1:D100).

Another example of functions. Suppose you want to find if the cell of month is greater than 1900 then we can give Bonus to Sales representative. The we can achieve it with writing formula with IF functions as =IF(B9>1900,”Yes”,”No”)

Function Arguments

In the above examples, you may have noticed that all the functions used parentheses. The information inside the parentheses is the list of arguments.
Functions vary in how they use arguments. Depending on what it has to do, a function may use.
• No arguments − Examples − Now(), Date(), etc.
• One argument − UPPER(), LOWER(), etc.
• A fixed number of arguments − IF(), MAX(), MIN(), AVERGAGE(), etc.
• Infinite number of arguments
• Optional arguments

Built-in Functions in Excel

Built In Functions

MS Excel has many built in functions, which we can use in our formula. To see all the functions by category, choose Formulas Tab » Insert Function. Then Insert function Dialog appears from which we can choose the function.

Functions by Categories

Let us see some of the built in functions in MS Excel.
• Text Functions
o LOWER − Converts all characters in a supplied text string to lower case
o UPPER − Converts all characters in a supplied text string to upper case
o TRIM − Removes duplicate spaces, and spaces at the start and end of a text string
o CONCATENATE − Joins together two or more text strings.
o LEFT − Returns a specified number of characters from the start of a supplied text string.
o MID − Returns a specified number of characters from the middle of a supplied text string
o RIGHT − Returns a specified number of characters from the end of a supplied text string.
o LEN − Returns the length of a supplied text string
o FIND − Returns the position of a supplied character or text string from within a supplied text string (case-sensitive).
• Date & Time
o DATE − Returns a date, from a user-supplied year, month and day.
o TIME − Returns a time, from a user-supplied hour, minute and second.
o DATEVALUE − Converts a text string showing a date, to an integer that represents the date in Excel’s date-time code.
o TIMEVALUE − Converts a text string showing a time, to a decimal that represents the time in Excel.
o NOW − Returns the current date & time.
o TODAY − Returns today’s date.
• Statistical
o MAX − Returns the largest value from a list of supplied numbers.
o MIN − Returns the smallest value from a list of supplied numbers.
o AVERAGE − Returns the Average of a list of supplied numbers.
o COUNT − Returns the number of numerical values in a supplied set of cells or values.
o COUNTIF − Returns the number of cells (of a supplied range), that satisfies a given criteria.
o SUM − Returns the sum of a supplied list of numbers
• Logical
o AND − Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise
o OR − Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise.
o NOT − Returns a logical value that is the opposite of a user supplied logical value or expression i.e. returns FALSE if the supplied argument is TRUE and returns TRUE if the supplied argument is FAL
• Math & Trig
o ABS − Returns the absolute value (i.e. the modulus) of a supplied number.
o SIGN − Returns the sign (+1, -1 or 0) of a supplied number.
o SQRT − Returns the positive square root of a given number.
o MOD − Returns the remainder from a division between two supplied numbers.
So, this brings us to the end of blog. This Tecklearn ‘Using Functions and Built-in Functions in Excel’ blog helps you with commonly asked questions if you are looking out for a job in Microsoft Excel. If you wish to learn Excel and build a career in Business Intelligence domain, then check out our interactive, Microsoft Power BI Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Microsoft Power BI Training

Microsoft Power BI Training

About the Course

Microsoft Power BI Training at Tecklearn will help you achieve expertise in business analytics Our best online training course teaches you all important concepts like Power BI Desktop, Mobile, Power Query & Power Pivot, Data modelling, visualization, creating dashboards and reports, DAX, etc. As part of this program, you will work on real-world projects. Also, our Power BI course curriculum will equip you with all the key skills that are required to clear the Microsoft Power BI Certification exam (70-778).

Why Should you take Microsoft Power BI Training?

• The average annual pay for a Power BI Professional is $114,000 . -PayScale.com.
• Cognizant, Dell, KPMG, Hitachi, Wipro, Avanade, Annik Inc, Brillio and 45,000 MNCs across 185 countries use Power BI and it has a market share of around 7% globally.
• Microsoft Power BI has been ranked at No.1 position in the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for 13 consecutive years.

What you will Learn in this Course?

Introduction to Microsoft Power BI
• Microsoft Power BI Introduction
• Power BI Products
• Power BI Architecture
• Installing Power BI
• Connecting to Data Sources
Power BI Workflow
• Key features of Power BI workflow
• Power BI Vs MSBI
• History of Power BI
• Power BI Products
• Data modelling and relationships
Power Query for Data Transformation
• Power Query for Data Transformation
• Learning about Power Query for self-service ETL functionalities
• Working with Excel data
• Introduction to Query Editor
• Data transformation
• Pivot and UnPivot
• Merge Join, relational operators, date, time calculations, working with M functions
• Summary Tables
• Writing custom functions and error handling
• M advanced data transformations
Filters and Drill Down Report
• Visualization Filter
• Page Level Filter
• Report LEVEL Filter
• DRILL Through Filter or Report
• BookMark Report
Power Pivot for Data Modelling
• What is SSAS
• Data Modelling
• Star Schema
• Snowflake Schema
• Introduction to MDDB and Tabular
• Data Access Modes
• Tabular with DAX
• Power BI with DAX
• DAX Functions
Data Analysis Expression – DAX Queries
• TABULAR with DAX
• Power BI with DAX
• Calculated Members, Row, Filter & Evaluation Context
• Cumulative Charts
• Calculated Tables, ranking and rank over groups
• DAX advanced features
Power BI Desktop & Administration
• Reports and Visualization
• Learning about data modelling and data relationships
• Deploying data gateways
• Reports and Dashboards
• On Premises Data Gateway
• Gateway Clusters
Introduction to Power Q & A
• Power Service
• Power Q & A best practices
• Integrating with SaaS applications
• Gateway

497