When getting an assignment at work, the first question that arises is “when should it be done?” and the answer “For yesterday!” it puts us in a situation to do it the way we know-how. It doesn’t always have to be the “smartest”, but it is the way we know and in the result we trust. On this occasion, we will use the most sought-after and/or most useful functions in Excel.
The solution to our problem can be using a known function or formula, and you can read about the differences in our article “The difference between a formula and a function in Excel”.
If this is a recurring task, we may need to find a better solution. New features in the latest versions of Excel should then be explored, which may provide answers to many questions in improving the solution. In the search for a solution, you should first look at the most sought-after and useful functions in Excel that other Excel users use, and perhaps there is already a solution in the form of a ready-made excel file or template.
Mathematical operations in EXCEL
Many data processing will be easily completed by mathematical operations of addition, subtraction, multiplication and division, and the most important thing is to know how to use the dollar! I mean not as a currency but as a sign $ A $ 1, when when copying mathematical operations some value is not variable. The best example of this is the exchange rate.
Let’s say we have a column with prices in euros [€] and we want to calculate the value in today’s second column at today’s middle exchange rate National Bank of Serbia. You can find more about absolute and relative addressing of table cells in Variables in Excel formulas.
Basic functions in EXCEL
If we have clarified this, we can now deal with the functions that first appeared in EXCEL, and this is probably the most useful summation formula.
| Function | Description |
|---|---|
| SUM | Use this function to collect the selected values (in one column, one row or several different values selected by intentional selection using the Ctrl button). |
| COUNT | Use this function to count values in cells. |
| AVERAGE | Use this function to calculate the mean value in the selected cells. |
Advanced functions in EXCEL
As you progress through the spreadsheet space, you will soon need to link data from multiple tables. Here you inevitably encounter the functions VLOOKUP, INDEX, MATCH, as well as many others.
| Function | Description |
|---|---|
| VLOOKUP | Use this function when you need to find something in a table or range that is filtered in order. For example, search for an employee’s last name by employee number, or search for a phone number by searching for the last name (just like in the phonebook). Watch this video on using the VLOOKUP function. (Details explained) |
| INDEX | Use this function to return a value or reference to a value from a table or range. Most requested functions in EXCEL |
| MATCH | Use this function to search for an item in a range of cells and then return the relative position of that item in the range. For example, if the range A1: A3 contains the values 5, 7 and 38, the formula = MATCH (7, A1: A3,0) gives the number 2 because 7 is the second item in the range. |
| CHOOSE | Use this function to select one of a maximum of 254 index-based values. For example, if arguments value1 to value7 are given per week, the CHOOSE function returns one of the days when a number between 1 and 7 is used as the number_index argument. |
| IF | Use this function to return one value if the condition is met and another if it is not. Here is a video on using the IF function. |
| DATE | Use this function to return a sequential sequence number that represents the date. This feature is most useful in situations where cell formulas or references provide year, month, and day. For example, you might have a worksheet that contains dates in a format that Excel does not recognize, such as YYYYMMDD.Use the DATEDIF function to calculate the number of days, months or years between two dates. |
| DAYS | Use this function to return the number of days between two dates. |
| FIND and FINDB | The FIND and FINDB functions find one text string within another text string. They give the starting number of the first text string from the first character of the second text string. |
| LOOKUP | Use this function when you need to search one row or column and find a value from the same position in another row or column. |
