«

»

Calculation of tenths, hundredths and thousandths with Excel

3 Thousandths 😱😱😱

Looking at results of speed skating in the Olympic winter games of 2014, the gap between the first and second of the 1500m race was only 3 thousandths.

Separate athletes with thousandth is extremely unusual and this give me the idea to calculate what represent 0.003 after 1500m race.

With Excel, it's easy to calculate the difference between two times. You just have to use the proper format for the time.

We know the distance (1500m) and the time for the athletes.

Let's create the following table with the time of the athletes

We see immediately the first problem: we have lost the thousandths 😤😱😭

 

Format of the tenths, hundredths and thousandths

In Excel, if you want to visualize the tenths, hundredths or thousandths to your Time, you need to add the value 0. Have a look at this article to see how to customize your time format.

  • Selecting the two cells that contain the time
  • Click on the little arrow to the right of the word number in the Ribbon to display the dialog box cell format. Or open the format dialog box with the shortcut Ctrl + 1

 

In the dialog Cell format, you select in the left menu, the Custom option in the text box, you write your own number format, as in this case

mm:ss.000

 

 

 

 

 

 

 

 

 

And instantly you see the thousandths in your spreadsheet 😍😍👍

 

 

 

Convert in seconds

In Excel, 1 is always 1 day and never 1 hour or 1 minute.

To solve our problem, we must convert the time in B4 by the number of seconds.

Because we must keep the thousandths we can't use this formula

=MINUTE(B4)*60+SECOND(B4)

 

The only way to keep the thousandths is to use this formula.

=B4*24*60*60

 

Where

  • 24 is the number of hours in a day
  • 60 is the number of minutes in an hour
  • 60 is the number of seconds in a minute

And we obtain C4 cell the time in seconds, with thousandths

Distance in 1 second

We have nearly finished 😃😉

Now, we have to calculate the distance covered by each athlete in 1 second.

The formula is simple: a division between the distance and the time

=$B$1/C4

Wow! An average of 14.28 meter per second , that's huuuuuuuge 😲😲😲

Difference between the two athletes

Now we just have to calculate the gap between each competitor

=D4-D5

 

Gap for the race

And finally multiply this result by the running time

=D6*C4

And we get a difference of 0.0429 m or 4.29 cm

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/calculation-tenths-hundredths-thousandths-excel/


Leave a Reply

Your email address will not be published. Required fields are marked *