Tag Archives: Excel

Excel Pace Calculator

Excel Pace Calculator

Excel Kalkulator Biegacza
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:

Download Free

You can learn similar tips and tricks in Excel at a comprehensive Training in London.

IFS vs nested IF

IFS vs. nested IF

Excel IF Statement

Syntax of Excel IF Statement:​

= 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. 

IFS vs nested IF

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 )
=IFERROR(IFS(A1>5,"Excellent",A1>3,"Good"),"Poor")

If the value in cell A1 is higher than five, the formula return Excellent, if not, but higher than three return Good, otherwise it return Poor.

You can learn similar tips and tricks in Excel at a comprehensive Training in London.

XLOOKUP vs VLOOKUP

XLOOKUP vs VLOOKUP

xlookup vs vlookup

Forget about the VLOOKUP function. The new XLOOKUP function has arrived, being a genuine alternative for VLOOKUP, HLOOKUP, INDEX and MATCH. If you know how to use the XLOOKUP function, you will never bother to use VLOOKUP anymore. The new feature, which has been recently introduced, has more options and is characterised by its flexibility, efficiency and user-friendliness.

XLOOKUP has six arguments:

= XLOOKUP ( Lookup_Value , Lookup_Array , Return_Array , If_not_Found , Match_Mode , Search_Mode )​​

1. Lookup_Value
2. Lookup_Array
3. Return_Array
4. If_not_Found
5. [Match_Mode] – default FALSE – Exact match
6. [Search_Mode] – default TRUE – Fist to last

Benefits of XLOOKUP over VLOOKUP:
✅ the searched result in the column may be placed to the left of the column with the searched value;

✅ change of the structure of the table with data by inserting or removing columns will not affect the result;

✅ we select only relevant ranges and not necessarily the entire table;

✅ it can return a range, not just a value;

✅ it can return a value, if not found;

✅ the exact search is set to 0 by default in argument 4, the most commonly used option;

✅ it can return the nearest higher value if we declare (1) in the 4th argument;

✅ should we look for the closest value in the 4th argument we do not need to have any data sorted;

✅ wildcard support only if you declare (2) in the 4th argument;

✅ it can search for the last value (-1) in argument 5;

✅ it carries out binary search;

✅ it performs searches not only vertically, but also horizontally if we mark the ranges accordingly;

✅ faster functionality.

You can learn similar issues and tricks at my comprehensive Excel Training in London.