Excel Functions vs. Formulas Explained
Translated by
ScribdTranslationsExcel Functions vs. Formulas Explained
Translated by
ScribdTranslationsThe main difference between an Excel function and a formula is that
a formula is an equation that the user writes to obtain a
result, while a function is a predefined formula to
perform more complex operations.
Now, to help you better understand the difference between formula and function,
let's see the concept of each of these operations.
Oneformulait is an equation that performs operations on the
data in a spreadsheet. Formulas are used to perform
mathematical operations such as addition and multiplication, or perform the
comparison of the content of a table.
Formulas have a character limit, so for example you can't
use a formula to sum, for example 1000 cells. To do this
You need to use a function, whose arguments are easier to edit.
Formulas can contain functions that facilitate the creation of a
operation.
OnefunctionIt is a predefined formula. The functions are small.
pre-designed macros to perform calculations using specific values
called arguments that are in parentheses. The arguments are
executed according to the structure of the formula, following a
specific sequence. Functions that do not have arguments need
accompanied by empty parentheses.
User-defined functions can be created using the VBA editor.
from Excel. This requires knowledge of programming and a little bit of
documentation to not forget the created macros.
Microsoft Office Excel has many built-in functions that
they are used to perform mathematical and logical operations with the data
found in the spreadsheet. In Excel, functions are formulas
simple ones that are inserted into a cell of the spreadsheet; either
writing the name of the function or using the window to insert
functions. The data used to work with formulas can be of type
numeric, text, date/time or other formulas. The functions allow for
perform calculations ranging from simple calculations to complex calculations such as
scientific, statistical, and financial equations.
Excel function types are classified as follows:
Database Functions
The database functions in Excel allow you to work with a database.
data, properly speaking, that is to say, that serve to manipulate large
number of data records. Basic operations can be performed
such as sum, average, count, etc., but include criteria of
arguments that allow to include some of the records from your database
data in the calculation.
Search and Reference Functions
These functions will help you work with data arrays, including
functions that search for and return the location of a given value.
Date and Time Functions
These functions are used to work with dates and times in Excel.
Information Functions
These functions mainly provide information about the
cell contents, such as the type and properties of the data.
There are also functions that provide information about the format or
location of a cell and the current operating environment.
Information functions were incorporated from Excel 2013, so
So, they are not available in previous versions of Excel.
Text Functions
Text functions allow manipulating character strings such as
customer names, street addresses, and product descriptions.
The LENGTH() function returns the number of characters in a fragment
The function UPPER() returns the text in uppercase.
CONCATENATE() combines multiple text strings into one
chain.
Statistical Functions
Excel has a variety of functions that can be used to obtain
statistics of a range of values. A common function is AVERAGE()
that calculates the average value of several values. The MAX() and MIN() functions
They are used to obtain the maximum and minimum values within a range.
Advanced functions allow obtaining harmonic and geometric means.
variance, etc.
Logical Functions
They allow for sophisticated calculations by testing the value of a
cell to perform operations depending on the result. The
The IF() function returns a true or false value depending on whether it
meets or does not meet a condition.
Mathematical and Trigonometric Functions
They are the most useful and used functions in Excel. The Functions
Mathematics and Trigonometry allow for the summation of a range of values with
the SUM() function, counting values with COUNT(), etc.
MATHEMATICAL FUNCTIONS IN EXCEL
SUMMARY:sum of several elements.
SUMIFsum of various elements with a condition.
SUMIF: sum of several elements with various conditions.
MMULT:multiply two matrices. Of matrix type.
COUNT:count the elements in a range.
COUNTIFS:count the elements of a list depending
of various conditions.
ENTERO:extract the integer from a value.
AVERAGE:calculate the arithmetic mean of a range of cells.
AVERAGEIFS:count values in a range that meet
certain characteristics.
LOGICAL FUNCTIONS OF EXCEL
This group of Excel functions allows us to use logic in our
Excel formulas.
SI: allows us to evaluate a condition and return a result if it
fulfill and if it is not fulfilled
Y: nest two or more conditions exclusively.
O:nest two or more conditions in a non-exclusive way.
TEXT FUNCTIONS IN EXCEL
RIGHT:returns a given number of characters starting from the
right.
LEFT:returns a given number of characters starting from the
left.
CONCATENATE:merge several cells or strings of text.
SPACES:remove the leading or trailing spaces from a text string.
LARGE:returns the length of a string.
SEARCH AND REFERENCE FUNCTIONS
INDEX:This function returns a value from a cell given its position in
a range.
VLOOKUP:This function returns a value associated with the searched value.
DESREF: This function returns the value of a cell given a starting point,
a row and a column.
COINCIDEthis function allows us to find a match in a range
and obtain your position.
CHOOSE:this function returns the value of an argument given a number
as a starter.
ADDRESS:this function returns a reference to a cell
INDIRECTthis function returns the value of a given cell
DATE FUNCTIONS
TODAY:this function returns the date of the day.
NOW:this function returns the date of the day and the time.
SIFECHA:this function allows creating a condition with a date.
[Link]:returns the number of working days between two dates.
YEAR:obtain the year from a date.
OTHER FUNCTIONS OF EXCEL
FILA:this function returns the row of the cell.
COLUMN:this function returns the column of the cell.
IT'S AN ERROR
RANDOM:this function returns a random number between 0 and 1.
IMPORTDYNAMICDATA:this function returns a value of a
dynamic table given some arguments of it.
[Link]:this function helps us to rank values from a
list and sort them from highest to lowest.
VAN:this function is used to calculate the NPV and the IRR of an investment.
To see a translation of all Excel functions from Spanish to
English and vice versa clickhere
More about Excel:
Pivot tables
Advanced Excel Course
Excel formulas
Excel Charts