@Q) 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. A 8 ¢ D £ Hem Food 1 2 3 Fare 4 School Material 5 Other 6 ‘Maximum Amount Spent| 8 per Week| ‘Average Amount Span] 9 per Week| 10 u Total Number of item with 40 budgel| R Personality Trail To get the BBO 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 =$ES7 * B2. And copy it to cell C3. The cellreference E7 did not change when you copied it to another cell because of the S symbol. The $ signs in the formulas are simply to prevent these references from adjusting, ‘To get PEE) 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. Funetion 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 Formula: sUM() Change the formula in cell B6 to =SUM(B2:BS). B2:B5 is the range of cells, which means you will start adding the value from cell B2 to cell BS. ‘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 SO 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 I 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 BIMIB for the [Average Amount spent per week], use the formula AVERAGE(D2:D5) ‘The AVERAGES 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 BS, 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 BS, 6. SUMIF( ) SUMIF adds all cells that meet certain criteria, Like, if you want to add all cells that have greater than S0 amount you saved in a week. The formula is =SUMIF(E2:E5,50). Enter this formula in cell £13. ‘The SUMIF) function is used to add all cells that meet certain criteria. The format is =SUMIF (range, criteria). Example: =SUMIF(E2:£5, “>50") adds all the cells with greater than 50 in to ES, Just enclose the criteria in open and close quotation marks. cells 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 IF9 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: =1F(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.) HOME RSERT —FAGELAYOUT FORMULAS DAR _ABVEW—_ YEW a) [32\0m YE eRe Getta Refesh Tatty ne Dalo- all> advanced —coumns £% Dats Vaidation + Connections sort a Fite Date ToolsChart A chart also called as graph is a tool to visually present data in different chart formats such as Ber, 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: BPE Ses & peilescrce et | i S| aad a iy BB WA ee — ‘Tdsd 1, Select cells A1:B5, 2. Go to the Insert ribbon, look for the Charts tab, click Insert Column Chart, and choose 3D column. The chart is now created. 3. You can add or modify the chart elements such as adding Legends by clicking the + icon located at the upper right corer of the chart. 4. Save your work.