How to calculate Mean Kinetic Temperature (MKT) in Excel Sheet (Free Download)

I presume you are dealing with medicines or other temperature sensitive products and have you wondered at some point about the calculation of MKT? Read on.

Mean Kinetic Temperature is calculated for understanding effects of variations in temperature for temperature-sensitive goods during storage and transportation.

Have you wondered the difference between Mathematical Average value and Mean Kinetic Value?

The calculation is different from the Average value which is the sum total of the values divided by the count of the values. Mean Kinetic value is slightly higher than average value.

This is particularly important for storage and transportation of medicine, vaccines etc.

A sample from our data logger in the image on the right side indicates the MKT value automatically generated in its report. Nowadays we do not need to calculate MKT manually. You can simply place a temperature data logger and the reports will automatically indicate the MKT value.

What is Mean Kinetic Temperature in basic language?

Free Excel software for automatic calculation of MKT of multiple data loggers

In layman’s language this is explained below:

As an example, we will consider that a medicine which has to be maintained between 2-8 °C is transported for 10 hours. A temperature data logger is kept inside the box for recording the temperature continuously. At the end of the transportation, the recorded temperature data is downloaded and verified. At this point suppose that the temperature has gone above 8 °C couple of times and below 2°C couple times. As the medicine is always to be kept between 2 and 8°C, you are not sure whether the medicine is acceptable or not since the temperature has gone above 8°C few times or below 2°C few times for different durations. In this case, you can check the MKT value of the 10 hours period and if the MKT value is within 2-8°C, the medicine is still considered to have retained all its properties.

Hence MKT for a substance can be defined as the calculated value derived from the complete recording over a specified period of time. If this value is within the permitted values, the substance is still considered to be safe.

How to Manually calculate Mean Kinetic Temperature in Excel Sheet?

Would you be interested in the simplest way using an Excel sheet? All of you are familiar with Excel and hopefully, this will be the simplest calculation method.

You may download the free excel sheet which defines a function namely “MKT”. This function can be used similarly to any other function such as SUM, AVERAGE etc.

If you do not want to calculate manually, we encourage you to Download the free Excel sheet

Manual calculation Step by Step

We once again encourage you to Download the free Excel sheet!!

If you are still adamant on calculating manually here we go:

First of all, you will download the data from the temperature data logger or temperature and humidity data logger. (MKT is applicable for Temperature only and not for Humidity) Assuming that the data is recorded at equal intervals, this is the formula for calculation of Mean Kinetic Temperature:

Delta H is the activation Energy : 83.14472  kJ/mole

R is the Gas constant : 8.314472 J/mole/degree

Conversion of °C to K : Add 273.15

Now we’ll go step by step starting with denominator, which is complex:

We have considered sample data with only six readings.

Step 1: Convert °C to Kelvin by adding 273.15 to each reading

The MKT value is always calculated in Degree Kelvin. If your readings are in°C or °F, the same have to be firstly converted to Kelvin. The resultant MKT value also will be in Kelvin, which then has to be converted back to your required units

Step 2: Calculate the following for each reading:

Delta H/(Gas Constant x Temperature Reading)

Both step 1 & 2 are detailed in the following:

Excel formula for above example for first reading

=Exp(-83.144/(8.314472 x 292.95)

Result =0.966440811349066 for first reading.

Step 3 : Calculate sum of all results of Step 2

This is simple! Simply sum up all values in column G.

Excel Formula in the above example for step 3:

=SUM(H8:H13)

Result = 5.79927272

Step 4 : Divide the result of Step 3 by number of readings

In this example, a total number of readings is 6. So divide the above results by 6.

Excel Formula in the above example for step 4:

Free Excel software for automatic calculation of MKT of multiple data loggers

=SUM(H8:H13)/6

Result = 0.966545453

Step 5 : Calculate natural logarithm of result in Step 4

Excel formula in the above step is

=(LN(SUM(H8:H13)/6)) x -1

Result =0.03403

Now denominator is  over.

Step 6 : Calculate Denominator

The denominator is Delta H divided by Gas Constant.

Result = 10

Step 7 : Calculate nominator by denominator

=10/0.03403

Result = 293.885

Step 8 : Convert Kelvin to Degree Centigrade

Download step by step guides for MKT calculation

Excel calculation method:

=293.882-273.15

Result= 20.7321 °C

Hence the MKT value is 20.7321 °C.

Free Excel Template Sheet for calculating MKT

Download the free Excel sheet here

All the data has to be listed vertically. At the end of the last cell simply insert the formula =MKT(B1:B100). This will produce MKT value of the range of values from B1 to B100. There is no limitation to the range and you may include any number of data. Simply replace the range B1:B100 with your corresponding range.

This excel sheet also calculates Minimum, Maximum, Average values of the range of temperature values.

Should you require any clarification on the same you may please contact us.

Explanation of formula for Mean Kinetic Temperature (MKT)

The calculation method is a bit complicated as below. Look puzzled? Go to the excel sheet above and do it by simple means.

The calculation formula for MKT is explained below. This formula is already incorporated in the Excel sheet above.

Short form of the formula for MKT

 Detailed formula for calculation of  MKT

Do you find the above calculation to be tough? Please feel free to Download the free Excel sheet!!

Temperature Dataloggers with inbuilt MKT value calculation

We supply various types of data loggers which have inbuilt calculation of Mean Kinetic Temperature. Our Temperature data loggers, as well as Temperature and Humidity Data Loggers, give the MKT value of the complete readings as part of the report. The MKT value is calculated from the first reading to the last reading. So when you receive a box of medicines, you can download the data from the temperature data logger and check individual values as well as the MKT value. A sample report of one such data logger is given in this image.

How to use data loggers for calculating MKT?

We will consider few examples of calculation of MKT using data loggers.

1. For transporting a box containing a medicine, you have to keep a temperature data logger inside the box. As soon as you keep the medicine inside the box, start the data logger and keep inside the box. Then you can despatch the box. When the user receives the box, he will take the data logger and download the data. Then generate the pdf report. The report will show the MKT value as in this image to the right side.
2. If you want to find out the MKT inside a cold room, you may keep Temperature and Humidity data loggers at the hot and cold points. If you want weekly data, you can start the data logger at the beginning of the week. You may keep a start delay of 10 minutes so that the recording settles down. At the end of the week, download the data and you may see the MKT value in the report.
3. Another application is for finding out MKT inside refrigerators (medical fridges). If you want monthly data calculation, you can keep the data logger inside the refrigerator after starting at the beginning of the month. At the end of the month, download the data and see the automatically generated report to see the individual readings and the MKT value.

Also please see our article: Is MKT still relevant for Pharmaceuticals? How will MKT misguide you?

Please visit our online data logger store for all types of data loggers.

Vacker Arabia supplies data loggers with automatic MKT calculation in all cities of Saudi Arabia covering Jeddah, Dammam, Riyadh, Jubail and Khobar.