Converting values and percentage increase in Microsoft Excel 2019

We will go through the steps to convert college marks to arrive at apercentage. Make sure you havethe ConvertingValues.xlsx workbook open.

Look at cell C6; the first student has achieved89 marks out of a total of 140. We need toconvert the student mark to a mark out of 125 instead of 140 and placethe answer in D6, as follows:

  1. Click on cell D6.
  2. Press =
  3. Click on the student mark in cell C6.
  4. Press /
  5. Click on the Test Total cell,C5.
  6. Press *.
  7. Click on cell D5.
  8. Press Enter to see the formula result, which is illustratedin the following screenshot:
ms office 611
  1. To copy a formula, we use the AutoFill feature—this skill was addressed inChapter 8,Formatting, Manipulating, and Presenting Data Visually.Position the mouse pointer on the + in the bottom right-hand corner ofthe cell. Hold the mouse down and drag the + sign to fill the formula to the remainingcells to see the converted mark out of 125 for the remainingstudents.
  1. To check that the formula has copied down correctly, double-click onone of the answer cells—for instance, D8. You will notice that theformula has copied down relative to the row and column from one cell tothe next. Although the formula is displaying no errors in this case,even though it is using the incorrect TestTotal and Mark Out Of, you willfind this a problem when working out the % for each of the students.Let’s investigate this!
  2. Click on cell E4, and enter the %heading. 
  3. Click on cell E6 to work out the firststudent’s %.  
  4. Type the following formula: =D6/D5
  5. Press Enter.
  6. The first student’s percentage is now visible in cell E6.Now, autofill cell E6 to E7:E11.
  7. Notice that the formula is now producing errors. Double-click oncell E9 to see the problem. We will need to ensure that each student’sMark Out Of is divided by cell D5 andthat the cell reference is made absolute so that it cannot movewhen copying down a formula, as illustrated in the followingscreenshot:
ms office 734
  1. Let’s fix the error and make cell D5 absolute. Double-click on cellE6 to view the formula. Select D5 by highlighting it, then press theF4 function key on the keyboard. Note that if you are using alaptop, you will need to press the Fn key and then press theF4 function key. The result can be seen in the followingscreenshot:
ms office 56
  1. Press Enter to update the formula in cell E6.
  2. Place the mouse pointer at the bottom right-hand corner of cell E6,then double-click on the AutoFillhandle (the + sign at the bottom right-hand corner of the cell—thisbasic skill was explained in the previous Excel chapter) to update theformula in the following cells, thereby fixing the error.

Leave a Comment