Embedded Files
WHAT IS IT
Microsoft Excel, the Microsoft’s spreadsheet software is considered the industry standard piece of software in data analysis and happens to be one of the most preferred software by investment bankers and financial analysts in data processing, financial modelling and presentation (Corporate Finance Institute, 2019). It is also used in education and small businesses to make computations easier. If you hate mathematics because of numbers and you’re head is aching because of computing, then there are techniques you can do in Excel to make it easier.
It is important to know the two basic ways to perform calculations in Excel: Formulas and Functions. A formula is an expression that operates on values in a range of cells or a cell while function is a predefined formula.
Simple Formula
Take a look at the worksheet below.
To get the 250 for [Total Allowance per Week], multiply [Days per Week] and [Amount per Day]. So the formula to be typed in cell C2 is =E7 * B2. B2 is the cell that contains the value for [Amount per Day] for Food. E7 is the cell that contains the value for [Days per Week] which is 5. If you will copy the formula to cell C3, it will be changed to =E8 * B3, where cell E8 is empty and will give a result of 0. Change the formula in cell C2 to =$E$7 * B2. And copy it to cell C3. The cell reference E7 did not change when you copied it to another cell because of the $ symbol. The $ signs in the formulas are simply to prevent these references from adjusting.
To get 150 for the [Overall Total] for Amount per Day, the formula to be typed in cell B6 can be =50+40+20+40. But the problem in this formula is that once you change your budget for food, the [Overall Total] remains the same. The technique to avoid that problem is to use the cell reference in the formula like =B2+B3+B4+B5. But what if you will add another item, the formula will get longer. The advanced technique you can do is to use functions.
Function is a predefined formula with a friendly name. It reduces lengthy manual entry of formulas. For example, =B2+B3+B4+B5+B6+B7 is a formula to get the sum. And the function equivalent to this formula is =SUM(B2:B7).
Advanced Formulas
Advanced Formulas
1. SUM( )
Change the formula in cell B6 to =SUM(B2:B5). B2:B5 is the range of cells, which means you will start adding the value from cell B2 to cell B5.
The SUM( ) function is used to get the sum of all the values. It has a format of =SUM(range of values).
Example: =SUM(C2:C5).
2. MIN( )
To get 50 for the [Minimum Amount saved per week], use the formula =MIN(E2:E5).
The MIN( ) function is used to get the minimum value from the range of values. It has a format of =MIN(range of values).
Example: =MIN(C2:C5)
3. MAX( )
To get 200 for the [Maximum Amount spent per week], use the formula =MAX(D2:D5).
The MAX( ) function is used to get the maximum value from the range of values. It has a format of =MAX(range of values).
Example: =MAX(C2:C5)
4. AVERAGE( )
To get 97.5 for the [Average Amount spent per week], use the formula =AVERAGE(D2:D5).
The AVERAGE( ) function is used to get the average(arithmetic mean) value of its arguments. It has a format of =AVERAGE(range of values).
Example: =AVERAGE(C2:C5)
5. COUNTIF( )
If you want to determine the [Total Number of Item with 40 budget], in cell E11, enter the formula =COUNTIF(B2:B5, 40). The criteria you set is 40. It means that you are looking the value 40 from cell B2 to cell B5.
The COUNTIF( ) function is used to count all cells that meet certain criteria. The format is =COUNTIF(range, criteria).
Example: =COUNTIF(B2:B5, 50) counts the number of cells with 50 from cells B2 to B5.
6. SUMIF( )
SUMIF adds all cells that meet certain criteria. Like, if you want to add all cells that have greater than 50 amount you saved in a week. The formula is =SUMIF(E2:E5,50). Enter this formula in cell E13.
The SUMIF( ) function is used to add all cells that meet certain criteria. The format is =SUMIF(range, criteria). Example: =SUMIF(E2:E5, “>50”) adds all the cells with greater than 50 in cells E2 to E5. Just enclose the criteria in open and close quotation marks.
7. IF( )
You can also find out your [Personality Trait] based on the total amount you spent for the week. If you spent more than 500 in a week then you are spender, otherwise you are a saver. To convert this into formula, in cell E12, enter the formula =IF(D6>500,”Spender”,”Saver”).
The IF( ) function is used to check whether a condition is met, and returns one value if TRUE, and another value if FALSE. The format is =IF([logical_test],[value_if_true],[value_if_false].
Example: =IF(D6>=1000,”Spender”,”Saver”) means if the value in cell D6 is greater than or equal to 1000, the value “Spender” is returned, otherwise return a “Saver”.
Other MS Excel Features that you can use.
Flash
Fill Flash Fill automatically fills in values. It is located at the Data ribbon as shown in the figure below. For example, you will fill up cells B3:B5 the same amount in cell B2. Delete the values in cells B3:B5. Make cell B3 as the active cell then go to the Data ribbon, and click it Flash Fill. (Note: Return the original data.)
Chart
A chart also called as graph is a tool to visually present data in different chart formats such as Bar, Pie, Line, Area, Doughnut, or Radar charts. You can easily create it. For example, you’re going to present your allowance for a day in a bar chart format. Refer to the figure below and follow the given instructions:
MARIA FATIMA E. VALDEZ
SHS ICT INSTRUCTOR
San Jose Del Monte National High School
CSJDM, Bulacan
Page updated
Report abuse