Here is a complete collection of Excel formulas that are most often used in the world of education / work. There is an example of its use & function, complete!
You want to calculate data using the app Microsoft Excel, but don't know the formula?
In addition to Microsoft Word, the Excel number processing application is also one of the essential applications used by almost all fields of work.
In fact, not a few school children or students have relied on this application to help with their daily tasks, you know, gang.
However, because Microsoft Excel has a lot of formulas to use, not everyone knows about the calculation formulas that this application can do.
Therefore, in this article, ApkVenue will share complete set of excel formulas and their functions which can help make your task easier. Want to know anything?
Complete set of Excel formulas and their functions
Just like the CMD command code on a Windows laptop, the formulas in the Microsoft Excel application are also very diverse and of course have different functions, gang.
But, take it easy! Below, ApkVenue has prepared various sets of complete Excel formulas and their functions so that they are easy for you to understand.
Basic Excel Formulas
Before discussing the more complicated ones, you should first understand the basic Excel formulas that are commonly used for calculations, gang.
For example, formulas for subtraction, addition, division, and so on. Well, for more details, you can see the discussion below.
1. Excel Subtraction Formula
Photo source: JalanTikus (Want to know about the Excel subtraction formula? Check out the explanation below).
Slightly different from other Excel formulas that usually use special word formulas to perform calculation commands, the Excel subtraction formula is very simple.
You just need to use minus operator (-) to reduce the value of the data as is usually done on a calculator, gang.
It's just that, just like using other Excel formulas, at the beginning of the Excel formula the subtraction must still provide a sign equal to (=).
2. Excel Division Formula
Almost the same as the subtraction formula, the Excel division formula also doesn't use a special word formula to do the calculations, gang.
You just use slash operator (/) to get the value of the division of the data you have.
In addition, the way to use it is the same, namely you have to put an equal sign (=) at the beginning to indicate that what you are writing is an Excel formula.
If the results have been obtained, you can also do printing alias print the Excel file if it is needed.
3. Excel SUM . formula
Photo source: JalanTikus (SUM is one of the most commonly used basic Excel formulas).
Of the many existing Excel formulas, the Excel SUM formula is probably one of the most used, yes, gang. Given the function of the SUM formula itself, namely for do the sum of data values.
Actually, you can use plus (+) operator to do the same calculation. However, this is considered less effective because you have to click one by one the values you have.
Meanwhile, with the SUM formula, you stay drag all data values at once so that it is faster and more practical.
4. Excel Multiplication Formula
The next basic Excel formula is multiplication. This formula is also certainly very frequent, right, you use it in doing calculations?
Well, to calculate multiplication in Excel itself is very easy. Here you just need to use asterisk operator or asterisk (*).
Meanwhile, how to use it itself is still the same as the Excel formula for subtraction, division, or addition, gang.
Complete Excel Formula Date & Time Function
Excel formula Date & Time function consists of Excel formulas that can be used to generate dates and times automatically.
| Excel formula | Function |
|---|---|
| DAY | Finding the value of the day |
| MONTH | Finding the month value |
| YEAR | Finding the year value |
| DATE | Get date value |
| DAYS360 | Calculate the sum of between 2 dates on the condition that 1 year = 360 days |
| EDATE | Returns a date serial number indicating the number of months before or after the specified date |
| EOMONTH | Returns the serial number of the last day of the month before or after the specified number of months |
| HOUR | Convert serial number to hours |
| ISOWEEKNUM | Generates the number of ISO weeks of the year for the date you have specified. |
| MINUTES | Convert serial number to minutes |
| NETWORKDAYS | Returns the number of working days between 2 specified date periods |
| NOW | Returns the serial number of the current date and time |
| SECOND | Convert serial number to seconds |
| TIME | generate serial number from specified time |
| TIMEVALUE | Convert time in text form to serial number |
| TODAY | Generates the serial number of the current date |
| WEEKDAY | Convert serial number to days of weeks |
| WEEKNUM | Converts a serial number to a number representing the order of the week of the year |
| WORKDAY | generate the serial number of the date before or after the specified number of working days |
| YEAR | Convert serial number to year |
| YEARFRAC | Returns a fraction of the year that represents the total number of days between the start date and the last date |
Complete Excel Formulas Math & Trigonometry Functions
Excel formula Math and Trigonometry functions consists of Excel formulas to perform mathematical calculations such as total, cosine, square, square root, and so on.
| Excel formula | Function |
|---|---|
| ABS | Determine the absolute value of a certain number |
| ACOS | Determine the arc cosine value of a certain number |
| ACOSH | Determine the inverse hyperbolic cosine of a certain number |
| AGGREGATE | Specifying aggregates in a list or database |
| ARABIC | Convert a roman numeral to an arabic number |
| SALTY | Determine the arc sine value of a certain number |
| ASINH | Determine the hyperbolic sine inversion of the number |
| ATAN | Returns the arc tangent of a certain number |
| ATAN2 | Determine the arc value of the tangent of the x and y coordinates |
| ATANH | find the inverse hyperbolic tangent of a certain number |
| CEILING | Rounds a number to the nearest whole number or to the nearest multiple of significance |
| CEILING.PRECISE | Rounds a number to the nearest whole number or to the nearest multiple of significance regardless of the sign of the number rounded up |
| COS | Find the cosine of a certain number |
| COSH | Find the hyperbolic cosine of a given number |
| DEGREES | Converting radians to degrees |
| EVEN | Rounds a number to the nearest even integer |
| EXP | Returns e to the power of (exponential) a certain number |
| FACT | Returns the factorial of a given number |
| INT | Rounds a number down to the nearest integer |
| LOG | Converts a number to a logarithmic value for a given base |
| MOD | Returns the remainder of division |
| SIN | Determine the sine of the number |
| SUM | Add up the desired numbers |
| SUMIF | Adding numbers with a certain desired criteria |
| SUMIFS | Adding numbers with certain desired criteria |
| TAN | Determine the tangent of a certain number |
Complete Excel Formulas Statistical Functions
Excel formula Statistics function consists of Excel formulas to process statistical data such as calculating the average, standard deviation, variance, t-table, and others.
| Excel formula | Function |
|---|---|
| AVEDEV | Calculating the average absolute balance of data points from the average value of the data |
| AVERAGE | Calculate the average value of numbers in a certain range or data |
| AVERAGEA | Calculates the average value of all components including numbers, text and logical values |
| AVERAGEIF | Calculates the average value of numbers in a certain range or data that meets a self-determined criterion |
| AVERAGEIFS | Calculates the average value of numbers in a certain range or data that meets several self-determined criteria |
| COUNT | Count the number of numbers in a self-defined range/list/cell |
| COUNTA | Count the number of texts/values in a self-defined range/list/cell |
| COUNTBLANK | Counts the number of blank cells (not containing) in a self-defined range/list/cell |
| COUNTIF | Counts the number of cells in a range/list/cells that meet one self-defined criteria |
| COUNTIFS | Counts the number of cells in a range/list/cells that meet some self-defined criteria |
| MAX | Determine the highest (maximum) value/number of a certain range/list/cell |
| MAXA | Determines the highest value (excluding numbers, text, and logical values) in a certain range/list/cell |
| MEDIAN | Determines the middle value of a number in a certain range/list/cell |
| MIN | determine the minimum/lowest value of a number in a certain range/list/cell |
| MINA | Determines the lowest value (excluding numbers, text, and logical values) in a certain range/list/cell |
| PROB | Determine the probability whose value is between two limits |
Complete Excel Formulas Search & Reference Functions
Excel formula Search and Reference function consists of Excel formulas that can be used to retrieve data from range certain criteria and/or with one or more specific criteria.
Well, in this Search & Reference function Excel formula, you can also find the VLOOKUP formula that is commonly used to find and match certain values.
| Excel formula | Function |
|---|---|
| ADDRESS | Retrieving data references as text to a single cell in a worksheet |
| AREAS | Counting the number of areas in a reference |
| CHOOSE | Selecting a value from a list of predefined values |
| COLUMN | Retrieve 1 column name in a reference |
| COLUMN | Retrieve multiple column names in a reference |
| HLOOKUP | Retrieve data from a range/array horizontally with the top row being the key point of data retrieval |
| HYPERLINK | Create shortcuts/links that can open related documents stored on network servers (intranet/internet) |
| INDEX | Selecting a value from a specific reference/range/array |
| INDIRECT | Retrieves a reference indicated by a text value |
| LOOKUP | Looking for a value in a specific range/vector/array |
| MATCH | Searching for a value in a specified reference/array |
| OFFSET | Getting the reference offset of a specific reference |
| ROW | Retrieve 1 row name from data reference |
| VLOOKUP | Retrieve data from a range/array vertically with the first column (far left) being the key point of data retrieval |
Complete Excel Formulas Database Functions
Excel formula Database function consists of excel formulas related to databases (data collections) that are important and specifically for use in data processing.
| Excel formula | Function |
|---|---|
| DAVERAGE | Calculates the average of the selected database entries |
| DCOUNT | Counting cells containing numbers in database |
| DCOUNTA | Counting non-blank cells in database |
| DGET | Extract a single record from a database that matches certain criteria |
| DMAX | Specifies the highest value of the selected database entry |
| DMIN | Specifies the lowest value of the selected database entry |
| DSUM | Sum the data in certain columns in the database that match the criteria |
| DVAR | Calculates the variance value based on the existing sample in the database entry |
Complete Excel Formulas Logical Functions
Excel formula Logic function consists of Excel formulas that aim to test a statement with a logical test.
You could say, maybe this Logic function Excel formula is one of the most used Excel formulas in the world of work, gang.
| Excel formula | Function |
|---|---|
| AND | Returns TRUE (True) if all criteria are met (TRUE) |
| FALSE | Returns a logical value False (False) |
| IF | Define a logical test with 2 or more criteria that are created by yourself |
| SINGLE IF | Finds the value if the condition is TRUE/FALSE |
| MULTI IF | Finds the value if the condition is TRUE/FALSE with multiple comparisons |
| IFERROR | Returns the desired result if a formula returns an error result (such as #N/A, #ERROR etc.) |
| NOT | Reverse the logic of the argument |
| OR | Returns TRUE if every argument is TRUE |
| TRUE | Returns the logical value TURE |
Excel Formulas You Need to Know and Often Use in the World of Work
Well, for those of you who need a collection of Excel formulas for work, of course there are a number of formulas that are often used and you must know.
One of them is the VLOOKUP formula which is currently increasingly being used. But apart from that, there are many other indispensable Excel formulas as below!
1. SUM
Excel formula functions SUM is to add up a set of numbers. You can add a number of numbers or columns to columns easily.
For example, you want to add up data from columns A1 to A25, then you just use this formula by selecting columns A1 to A25.
- Formula: =SUM(number1:number2)
- Example: =SUM(L3,L7)
2. AVERAGE
The next formula that is often used is AVERAGE. This formula has the main function to find the average value of a variable.
- Formula: =AVERAGE(number1:number2)
- Example: =AVERAGE(L3:L6)
3. IF
Function IF is to take one of two values based on a condition. The logic is the same as IF in programming languages. In one function, you can put multiple IFs depending on your needs.
- Formula: =IF(logical_test,[value_if_true],[_if_false]
- Example: =IF(2 "True" =IF(2>3,"True","False")> "False"
4. COUNT
The most frequently used Excel formula that is almost similar to SUM is COUNT. This formula is used to count the number of cells that contain numbers.
- Formula: =COUNT(value1,[value2],..]
- Example: =COUNT(a1:a10)
5. MIN
To find the lowest number value among the amount of data, you can use the formula MIN. This formula returns the lowest value in a row of a series of numbers.
- Formula: =MIN(number1:number2)
- Example: =MIN(L3:L6)
6. MAX
The opposite of MIN, Excel formula MAX can be used to determine the highest value in a row of a series of numbers.
- Formula: =MAX(number1:number2)
- Example: =MAX(L3:L6)
Those are some of the most frequently used Excel formulas. To find out other Excel formulas that are often used in the world of work, you can check the article below:
VIEW ARTICLE Well, that was a complete collection of Excel formulas that can help you to complete calculations more easily and practically, gang.
Actually, there are many other Excel formulas that you can use, but the collection of Excel formulas that ApkVenue above are some of the most frequently used.
Which Excel formulas do you use most often?
Also read articles about Out Of Tech or other interesting articles from Shelda Audita
