Defining names in Excel involves giving a range of cells a unique name, which means that you can easily refer to a specific defined range, e.g. while creating a function. This is especially applicable in the following functions:
as the first argument
MATCH as the second argument
VLOOKUP and HLOOKUP as the second argument.
Formulas containing names instead of ranges are more understandable and easier to use, provided that the names we give will be legible. These names are also used in VBA programming.
In the Formulas card you will find the Defined names section:
How to define name for the range?
• Select the relevant range. • In the Name Box, enter the unique name of the selected range. Do not use any illegal special characters, e.g. spaces, $, @, #,! or arithmetic operators (+, -, /, *, ^), comparison (<> =) and the text operator (&). • Accept by pressing the Enter key.
Instead of the Name box you can click Formulas card then Define Name, set up: the name, scope, comment and refers to. In addition, the defined name cannot only refer to a cell or range of cells, but it also contains a formula or a specific value or text.
Create multiple names from the selection
• Select a range of data which contains multiple columns or rows with the labels at the beginning or the end. • Click Formulas card then Create form Selection or shortcuts[Ctr] + [Shift] + [F3]. • Select options based on what you want to name.
Name Manager
• Click Formulas card then Name Manager [Ctrl] + [F3]. • Create another new name, edit existing name, delete it, filter it and sort it
Insert the defined name into the formula
Simple click [F3] to obtain a list of created names and select the relevant one or start typing existing name and select the relevant one.
The Excel Runner Pace Calculator, is available for download at the bottom of the article or in the Downloads tab and it offers the following functionalities:
• After declaring the Event and Distance, it automatically calculates Speed and Pace in kilometres and miles. • After declaring the Pace and Units, it automatically calculates Speed and Time to cover popular Distances and Marathon laps. • Converts between Pace and Speed • Converts between SI and Imperial metrics. • Dynamic visualisation. • Pace band.
Definition of Pace and Speed
The Pace is the time, most often given in minutes, that is needed to cover 1 km. Alternatively, we give time to cover 1 mile or to cover a special distance, e.g. a lap.
Pace = Time / Distance [min/1km] or [min/1mil]
The Speed is the distance of time. In the SI system, we most often give the number of kilometres we manage to cover in one hour [km / h] or the number of meters per second [m / s].
Speed = Distance / Time [km/1h] or [miles/1h]
Convert miles to kilometres:
=CONVERT(1,"mi","km")
In the first argument of the function, instead of 1, we can insert the address of the cell in which the number we want to convert is given.
1 mile = 1.609344 kilometres. In Excel, the formula is used to
Setting the runner’s calculator.
Fill the yellow cells in the red border only. The rest of the spreadsheet is fully automated and write-protected.
In the upper part of the spreadsheet after declaring the Target (it is important to keep the hh: mm: ss format) and Event (from the drop-down list) the Pace and Speed in kilometres and miles are given.
2nd part of Pace Calculator setting:
Pace or time given in the correct format hh: mm: ss.
Unit to choose [kilometres] or [miles] from drop-down list.
Leap in seconds in the Pace column between rows.
Converts between Pace and Speed.
Knowing the Pace, saved in the hh: mm: ss format in an Excel cell (e.g. C15), we can calculate the Speed using the formula:
=24/(C15*24)/24
Calculating the time needed to overcome various distances based on the declared Pace. If in column C starting from C15, the pace is given in the format hh: mm: ss, and in line 15 starting from G15 the distance to do, then the formula in this case for the cell G15 looks in the following way:
=($C15*24)*G$13/24
The dollar symbol ($) in front of the letter C in cell addressing means that regardless of where you paste the formula, the addressing of column C will remain the same, but if it appears before the number, e.g. 15, the addressing of row 15 will remain unchanged.
The graph on the left shows the time to cover popular Event based on the declared Pace.
At the bottom of the spreadsheet there is a table with the Actual Pace declared in Miles. In the yellow fields we give the real Time achieved in individual sections of the marathon. As a result, we can control how the Plan is implemented in relation to the assumed Pace. In the Difference column, the difference when the implementation of the plan is negative is highlight in red.
Pace Band Worksheet Settings.
Set up your preferences:
Pace (hh:mm:ss format) [miles]
Strategy (drop down list)
Positive Split
Even Splitting
Negative Split
Deviation [%]
Download Excel Runner Pace Calculator by clicking button below:
= IF ( Logical Test , Value if True , Value if False )
Logical test Examples:
• Equal to: A1=A2. • Higher than: A1>A2. • Higher or equal than: A1>=A2. • Less than: A1<A2. • Less or equal than: A1<=A2. • Not equal to: A1<>A2.
If we do not declare a third argument, because it is not mandatory, it will return FALSE by default. If we want to return the text as a result in the second or third argument, it must be enclosed in quotation marks.
=IF(A1>5,"Pass","Fail")
If cell A1 value is higher then five, formula return Pass otherwise Fail.
In the case when we want to apply one logical condition, creating an IF function is simple and obvious. However, if we want to introduce further conditions, everything gets slightly more complicating. In this case, the function is nested, the next function (function in function or multiple) as an argument of the function.
Nesting of the IF function
I think each of us, at least once, has found it difficult to master the IF function when it is nested. That is, when the IF function was an argument of the IF function at least once. In addition, the nested function is a burden on the work of the system during conversion, of course, when we often use this function in the worksheet. I will now give you a simple example of an IF function nested once. In place of the third argument, a new IF function containing another condition is introduced.
= IF (( Logical Test , Value if True , IF ( Next Logical Test , Value if True , Value if False ))
=IF(A1>5,"Excellent",IF(A1>3,"Good","Poor"))
If cell A1 value is higher then five, formula return Excellent, if not but higher than three return Good, otherwise Poor.
IFS function alternative
In 2016, an alternative to the IF function appeared in Excel, i.e. the IFS function, which gives the possibility to introduce more logical conditions than one, which simplifies the creation of the formula and significantly speeds up its work.
= IFS ( First logical Test , Value if True , [ Next Logical Test , Value if True ] , ...)
=IFS(A1>5,"Excellent",A1>3,"Good")
If cell A1 value is higher then five, formula return Excellent, if not but higher than three return three, otherwise return error #NA.
It can happen that more than once the condition is met in the arguments of the IFS function. In this case, the one that is recorded first is decisive.
=IFS(A1>3,"Good",A1>5,"Excellent")
In this case, if the content of cell A1 is higher than three, the function will always return Good (never return Excellent). Therefore, the order in which the conditions are saved is important. If none of the conditions are met, the formula will return error #NA.
Benefits of IFS over nested IF:
• The IFS function allows you to easily enter further logical conditions without nesting. • Due to the lack of nesting, the performance of IFS is much more efficient • Allows you to apply twice as many, or 127 conditions, while the nested formula 64 conditions only. • If none of the conditions are met, IFS will return error #NA. In this case, it is worth using IFEROOR. In this case, in the second argument we give what should be if an error occurs, if we need it.
= IFERROR ( IFS ( Logical Test , Value if True , Next Logical Test , Value if True ) , Value if False )
