Excel It is one of the most popular applications in the world, tremendously practical for both professional and personal use. A simple but powerful tool with which to handle huge amounts of data, simply and quickly. Logically, to get the best performance from this instrument, it is essential to know it well. This will allow us, for example, to know what the difference between formula and function in excelInter alia.
And it is that, without a doubt, one of the strong points of this program is the fact that it allows us to create formulas through which to handle a lot of data at the same time and obtain all kinds of results. This is especially important in companies, even if they are small businesses, when it comes to managing inventories, commercial contracts, invoices or employee payroll. Excel formulas and calculations help us to expedite work.
To understand well what is the difference between formula and function in excelThe first thing to be clear about is that both concepts are closely linked. So much so that it could be said that they are practically the same. The functions are predefined formulas integrated into the program and are called like this because it can be interpreted that they are part of the functions or "services" that Excel offers us to save us time.
From the foregoing it can be deduced that the name “formula” must be applied to the rest of the formulas, that is, those that are not predefined, but are created by users according to their preferences and needs at all times. The difference is subtle, but important.
What is an Excel formula?
An Excel formula is nothing more than a code of symbols and numbers that is entered into a cell. Each of these symbols has a meaning and a function. And all together coordinate to perform a certain calculation whose result will be reflected in the cell itself.
All Excel formulas must begin with the equals symbol (=). The syntax is important. Everything must be written following the required order. The symbol "=" must be followed by the function or calculation that you want to perform and this by the cells on which you want to perform the calculation. We illustrate it with a simple example:
Here the "Sum" function has been used to calculate the sum of the values of three cells (B4, C4 and D4) so that the result appears in cell E4. At first glance we can see that the sum of 200 + 300 + 300 will give us a result of 800. In this case, we must go to the result cell and write the following:
=SUM(B4:D4)
Actually, since it is a default function, you will not need to write SUM, just simply select the function from the toolbar. To select the cells that contain the values to add (those known as "arguments"), we can use the mouse. Afterwards, we just have to press Enter or validate the formula for it to be applied.
At first glance, it may seem unnecessary to use a formula for such a simple operation, however, its use is incredibly practical when we have to deal with very long spreadsheets and hundreds, or perhaps thousands, of cells on which to apply calculations. much more complex.
Most used Excel functions
Next, a list of the most used Excel functions, ordered by categories. They are not all, although the most important ones are. In uppercase, the text that goes right after the "=" sign and before the arguments:
Search and reference
- SEARCH: Search for the values of a range of a column or a row.
- HLOOKUP: Searches in the first row of a table or array of values.
- VLOOKUP: Search for a value in the first column from the left of a table.
- COLUMN: It gives us the column number of a reference.
- CHOOSE: Choose a value from a list based on an index number.
- ROW: Returns the row number of a reference.
- HYPERLINK: Create a shortcut to a document stored on the hard drive or on the Internet.
- TRANSPOSE: Returns a vertical range of cells as a horizontal range, and vice versa.
Text
- CONCATENATE: Joins several text elements into one.
- FIND: Returns the starting position of a text string.
- SPACES. Removes all spaces from the text, except those between words.
- UPPER: Changes a text string to uppercase.
- LOWER: Changes all letters from one to lower case.
- CURRENCY: Changes a number in text with currency format.
- VALUE: Changes a text argument representing a number to a real number.
Confidential Information
- BDDESVEST: Calculates the standard deviation of a database.
- BDEXTRAER: Extracts a single record matching the specified conditions from a database.
- DPRODUCT: Multiplies the values in a column that match the specified conditions.
- DAVERAGE: Calculates the average of the values in a column or list or base under specified conditions.
Maths
- QUOTIENT: Calculates the integer part of a division.
- COMBINES: Shows the number of combinations with repetitions of a certain number of elements.
- INTEGER: Rounds a number to the nearest lower integer.
- EXP: Calculates a number raised to a certain power.
- LN: Calculates the natural logarithm of a number.
- LOG: Calculates the logarithm of a number to the specified base.
- GCD: Calculate the greatest common factor.
- LCM: Calculate the least common multiple.
- NUMERO.ARABE: Changes the Roman numerals to Arabic.
- ROMAN.NUMBER: Otherwise, change Arabic numerals to Roman numerals (in text format).
- PRODUCT: Multiplies all numbers specified as arguments.
- ROOT: Calculates the square root of a number.
- SUM: Adds all the numbers in a range of cells (it is the function that we have seen in the example).
financial functions
- AMORTIZ.LIN: Calculates the amortization of each of the accounting periods.
- AMORTIZ.PROGRE: Calculates the amortization of each accounting period using a specific amortization coefficient.
- INT.ACCUM: Calculates the accrued interest of a security that pays periodic interest.
- INT.ACC.V: Calculates the interest earned for a security that pays interest at maturity.
- EFFECTIVE INT: Calculates the effective annual interest rate.
- YIELD: Calculates the yield of a security that earns periodic interest.
- NOMINAL.RATE: Calculates annual nominal interest rate.
- IRR: Calculates the internal rate of return of an investment.
Statistics
- STDEV.M: Calculates the standard deviation of a given sample.
- LINEST: Calculates statistics that describe a linear trend that coincides with known data points.
- LOGST.: Calculates statistics that describe an exponential curve, coincident with known data points.
- FREQUENCY: Calculates the frequency with which a value occurs within a range.
- BoundedMean: Calculates the mean of the inner portion of a set of data values.
- MEDIAN: Calculates the median or middle number of a set of numbers.
- MODE.ONE: Calculates the most frequent or repetitive value of a data range.
- AVERAGE: Calculates the average (arithmetic mean) of the arguments.