INTODUCTION
You do not need to memorize hundreds of Excel formulas to start being quite proficient with the software. The truth is that for any given job, you will likely use the same 10-20 formulas repeatedly as you perform that job. In this chapter, we will get you stared with some of the common formulas. That you may need to know based on the 5 most popular and widely used formulas in Excel.
STRUCTURE
In this chapter, we will discus the following topics:
- SUM function
- IF function
- LOOKUP function
- VLOOKUP function
- CHOOSE function
OBJECTIVES
After studying this unit, you will be able to use the most common Excel functions in Excel and determines the appropriate situations to apply these formulas.
SUM function
The SUM function is one of the most popular Excel functions. It basically allows the user to quickly and easily add up or ”sum” a series of numbers together together. They is very useful if you have rows or column’s of figures you want to add.
THE FORMULA
=SUM(Reference Cells)
ERROS to avoid
Oftentimes SUM will be used in one row or column and then column then dragged over or down to be applied formulas like SUM from one cell to another cell. To do this, highlight the cell or cells you want to drag and drop and drop, then click hold down the bottom right corner of the selection box, and finally, drag your mouse along.
IF function
The IF function is one of the most useful functions in all of Excel. Essentially, it lets the user go through and create a simple IF-THEN type statement based on other cells in an Excel sheet. This us flag cells that are of particular interest from a large mass of data. For example, we might use the IF function to find the first data point in a sheet of panel data.
The formula
=IF(Reference Cells and condition, Value if condition is true, Value if condition is false)
The following figure shows the IF function being applied in EXCEL:
LOOKUP function
The LOOKUP function is similar to the SUM function in that it does a simple but very common task- it looks up a value in a based on a reference.
Uses and application
LOOKUP function are generally used when dealing with large datasets and the need to find something in the data without having to search for it manually or perform a Ctrl +F.
Errors to avoid
The most common error that comes up when dealing with lookup values is that of failing to put the array in properly.
Similarly, if there are multiple instances of the value to looked up, the LOOKUP function does not give the user any indication of the problem, so it is important to be very careful that your lookup value is a value is antique identifier and does not give the user any indication of the problem, so it is important to be very careful that your lookup value is a unique identifier and does not have duplicates in the data.
VLOOKUP function
The VLOOKUP function, along with the IF function, is probably one of the most versatile tools in Excel and also one of the most widely used in more advanced Excel models. In essence VLOOKUP lets the user lookup values, but without the limitation of the standard LOOKUP function.
USES and applications
VLOOKUP is useful not only for looking up a single value like a product price but, also for building a table that looks up looks up value from a larger set of data-for instance, we might lookup prices for half a dozen parts a customer is buying in order to determine the price of the customer’s complete order.
The formula
=VLOOKUP(cell with value to be looked up, the set of data to be searched for the value, the column number for the data to be returned, approximate or exact match represented buy a 1 or 0)
CHOOSE function
The CHOOSE function is similar to set of IF formulasit enables the user to specify what should be done if a cell meets certain criteria.
Uses and applications
The CHOOSE function is often used with or dropdown in Excel to allow a financial model to change based on user inputs, such as assumption about economic conditions or interests rates.
The formula
=CHOOSE(Reference cell where the input number is, value to be used when input is 1, value to be used when input value is 2, value to be used when is 3, and so on)
Errors to avoid
The most common error with CHOOSE is probably an error or mismatch that occurs when an input value if used-for instance, the users wants to show growth of 2% and instead shows growth of 3% simply because they had not been careful in setting up the formula.
