Excel is one of the most helpful and versatile programs on the market, and is used frequently by many of our readers. Its versatility stems from the many different areas in which it is used – from business intelligence to mechanical engineering. Helpful because of the valuable functions and formulae. But it is precisely this aspect that gets many beginners breaking out in a cold sweat. So we have taken a look at the most common Excel functions to give novices a better grasp of the world’s most commonly used spreadsheet calculation program and present to you: The five best Excel functions for beginners.
An article by the COMPAREX Editorial Team
The following takes an imaginary race as an example to describe five standard functions. First of all we have to decide which information is necessary and which Excel functions would be useful in this respect.
The input is spread over four columns:
This data should be used to calculate the following:
- The total race time for each runner
- A bonus for each runner who improved from lap 1 to lap 3
- A rating of each final result
- The average race time for all runners
Excel will be asked to automatically display this additional information. The following functions are used to make this happen:
The function =SUM() is used in the first step in order to enter the time that each contestant needed to complete the race. The information is added to column "E". John Doe’s lap times are found in the cells "B2", "C2" and "D2". These values are added to the area between the parentheses separated in each case by a semicolon: =SUM(B2; C2; D2).
The following animation shows how this function can easily populate all of the cells located below.
The column immediately adjacent will be used to add a bonus for all runners who improved their performance. This uses the =IF() function.
The basic rule is: "A runner who is faster in the third round than in the first round will receive a bonus of five seconds that is deducted from the total race time.”
Inside of the =IF() parentheses, the structure is always as follows (If; Then; Else).
If everything is now added up, the information inside the parentheses can be translated as follows:
"If lap 1 is greater than lap 3;
Then 5 seconds will be deducted from the total result;
Else the normal total result will be shown."
The sum function in column "E" states the total race time, so 5 units will be deducted from "E", or: "E-5".
The values for laps 1 and 3 are shown in columns "B" and "C". Again taking John Doe as an example, the =IF() function would look like this: =IF(B2>D2;E2-5;E2)
This function then populates the following cells as well.
The extremely versatile function =VLOOKUP() is used to categorize the runners' results and to show the rating in the respective line. =VLOOKUP() needs a matrix that is used to assign results to a rating.
- All results under 64 seconds are "excellent"
- All results up to 77 seconds are "good"
- All results up to 90 seconds are "okay"
- And all other results up to 150 seconds are "poor"
The first step is to define the search criterion and then the matrix. Excel will be asked to use the total race time including any bonus as the search criterion. The spreadsheet (see picture below) currently shows this value in column "F". The matrix should search for this value. If the value does not exist, the next higher, available value should be taken. The matrix must then display the rating in the third column (good, poor, etc.).
This would be as follows for the John Doe example: =VLOOKUP(F2;$I$2:$K$5;3;TRUE). The search criterion must be compared in the matrix with the next higher value in order to display the rating in each case (index line 3).
The information stating the matrix value is fixed. It can be adjusted using the "$" value. This fixing means that when the function populates all lines located below, Excel will only update the value of the search criterion (F2) while leaving the matrix values themselves unchanged.
The last function is =SUBTOTAL(). It can be used to calculate the average of all performances so far. A rough translation of the function would be as follows. =SUBTOTAL(Which function do we need?; Which values should be included in the calculation?).
In practice it would look like this: =SUBTOTAL(1;E2:E20).
This function has other sub-functions that are accessed using an index. 1 stands for the average. Other sub-functions like 9 mean the sum, while 4 is the maximum. Read more
The calculated average can now be used to adjust the bonus in different ways, for instance by awarding a bonus to all runners who were slower than the average while still improving from the first to the last lap. This slightly more complex operation is completed by nesting two functions. This uses the IF() function and the AND() function, which enables us to consider two variables.
A bonus of 10 seconds is deducted from the total race time of each runner whose lap 1 is greater than lap 3 AND whose overall race time is greater than the average race time; else the total race time will remain unchanged.
In practice, this function would be as follows, again using John Doe as an example: =IF(AND(B2>D2; E2>$F$22); E2-10;E2).
Note: The field for the average race time will be blocked again, as it is not meant to change, even if this function is assigned to a different line as well.