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

mean-kinetic-temperature-vacker

If you are dealing with medicines or other temperature-sensitive products you can calculate Mean Kinetic Temperature using this MKT calculation sheet. Read on.

What is Mean Kinetic Temperature in basic language?mean-kinetic-temperature-report

If you have a set of temperature readings across a certain period of time, the Mean Kinetic Temperature across this period means the effective average thermal value for this period. This temperature value is what the stored goods effectively feel during the mentioned time. This is not the arithmetical average of the readings.

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

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.

Have you wondered about the difference between the Mathematical Average value and the 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. The mean Kinetic value is slightly higher than the average value.

This is particularly important for the storage and transportation of medicine, vaccines, etc.
Data-logger-with-inbuilt-MKT-calculation

Example of application of Mean Kinetic Temperature

In layman’s language this is explained below:

As an example, download-free-mkt-calculation-excel-sheetwith Free Excel software for automatic calculation of MKT of multiple data loggers, we will consider that medicine that 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 a couple of times and below 2°C a couple of 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 a few times or below 2°C a 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.screen-mkt-calculation-in-excel

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 Calculate Mean Kinetic Temperature Manually in Excel Sheet?

Would you be interested in the simplest way of 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

The step-by-step method of calculating MKT is as below:

  1. Convert °C to Kelvin by adding 273.15 to each reading.
  2. Calculate Delta H/(Gas Constant x Temperature Reading). Delta H =83.14472  kJ/mole & Gas Constant = 0.008314472 kJ/mole/degree.
  3. Calculate the sum of all results of Step 2.
  4. Divide the result of Step 3 by the number of readings.
  5. Calculate the natural logarithm of the result in Step 4.
  6. Calculate the numerator Delta H divided by Gas Constant.
  7. Calculate the result of Step 5 by the result of Step 6.
  8. Convert Kelvin to Degree Centigrade. This gives the MKT value.

Data-logger-with-inbuilt-MKT-calculation
The detailed method of the above steps is explained below:

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 the calculation of Mean Kinetic Temperature:

MKT-calculation-simple-formula

 

 

 

 

Delta H is the Activation Energy: 83.14472  kJ/mole

R is the Gas Constant: 0.008314472 kJ/mole/degree

Conversion of °C to K: Add 273.15

Now we’ll go step by step starting with the 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 has to be first 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 steps 1 & 2 are detailed in the following:

MKT-calculation-step1-and-2

 

 

 

 

 

 

 

 

 

Excel formula for above example for first reading

=Exp(-83.14472/(0.008314472 x 292.95)

Result =1.49714 E-15 for first reading.

Step 3: Calculate the sum of all results of Step 2

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

MKT-calculation-step3

 

 

Excel Formula in the above example for step 3:

=SUM(G11: G16)

Result = 1.00339E-14

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

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

MKT-calculation-step4

 

 

 

Excel Formula in the above example for step 4:

download-mkt-calculation-excel-sheet

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

=SUM(G11: G16)/6

Result = 1.67232E-15

Step 5: Calculate the natural logarithm of the result in Step 4

 MKT-calculation-step5

 

 

The Excel formula in the above step is

=(LN(SUM(G11:G16)/6)) x -1

Result =34.0246

Now the denominator is over.
Data-logger-with-inbuilt-MKT-calculation

Step 6: Calculate numerator

The numerator is Delta H divided by Gas Constant.

MKT-calculation-step6

 

Step 7: Calculate the numerator by the denominator

MKT-calculation-step7

 

 

 

=10/34.0246

Result = 293.903

Step 8: Convert Kelvin to Degree Centigrade

MKT-manual-calculation-complete

Download step-by-step guides for MKT calculation

Excel calculation method:

=293.903-273.15

Result= 20.7530 °C

Hence the MKT value is 20.7530 °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 an MKT value in 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: excelB100 with your corresponding range.

This excel sheet also calculates Minimum, Maximum, and 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

calculate-mean-kinetic-temperature-MKT

 

 

 

 

 

 

 

 

 

 The detailed formula for the calculation of  MKT

formula-of-mean-knetic-temperature-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 an 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.
Data-logger-with-inbuilt-MKT-calculation

How to use MKT for GDP applications?

MKT is commonly used in the storage and transportation of medicines, vaccines, etc. The common applications are listed below:

  1. When medicine is stored in a warehouse or any storage facility, there might be temperature fluctuations. Assume that when you accept the goods, the temperature recorded till then was in perfect condition and you shifted to your storage. You might use a new data logger and commence recording. A few days or weeks later, you take the medicine for onward despatch. At this point, you first check whether the temperature has been always within the limits. You find that the temperature exceeded a few times beyond the limits. In such a case, you will check the MKT value to decide whether it is usable. If the MKT is within the limits, then medicine is assumed to be good.
  2. Similarly, during the transportation of medicine through various modes such as air, truck, etc. At the end of the journey, you might notice that a few temperature escalations happened during the journey. Hence you will check the MKT before accepting or rejecting the goods.

This is a regular practice that was commonly practiced until a few years ago. Now various local authorities and medicine manufacturers do not accept MKT as a standard practice. Hence you may check before completely going by the criteria of MKT.

How to use data loggers for calculating MKT?

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

  1. For transporting a box containing 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 it inside the box. Then you can dispatch 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 yodata-logger-with-automatic-mkt-calculationu 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.

Real-Time Monitoring of Temperature and MKT during transportation

With technological advances, it is now possible to monitor MKT during the transportation of medicines. So during transportation, it can happen that temperature continues to increase, but the MKT continues to be within the limits. In such a case, you can take corrective actions if you have such real-time monitoring systems. If you have multiple boxes inside a refrigerated van, you can have monitoring sensors in each box. So you can monitor each box separately. The wireless monitoring system by Roambee provides such a monitoring system: https://blog.roambee.com/supply-chain-technology/mean-kinetic-temperature-mkt-calculation-should-be-real-time

How to use our Excel Calculation Sheet?

The entire calculation explained above is programmed in the excel sheet. If the readings are in Degree Centigrade, you have to simply use the function =MKTc after marketing the desired range. This function is exactly similar to the usage of =SUM in Microsoft Excel. If your readings are in Degrees Fahrenheit, you have to use the function =MKTf. It is that simple.

Data-logger-with-inbuilt-MKT-calculation

FAQ on Mean Kinetic Temperature (MKT)

  1. What is the Mean Kinetic Temperature?
    If a temperature-sensitive product is stored across different temperatures in a specific range of time, MKT calculation gives an idea of the effects of temperature on this product.
  2. How to calculate Mean Kinetic Temperature (MKT)?
    In order to calculate MKT, we need temperature measurements at regular intervals. We calculate MKT over a specific period. Then we can use these data in the MKT formula to calculate the effective MKT.
  3. MKT is applicable for which type of products?
    Mean Kinetic temperature is applicable for any temperature-sensitive products. The major application is in the Pharma industry during storage of medicines, vaccines etc.
  4. How to use Excel for calculating Mean Kinetic Temperature?
    MKT is calculated as per a mathematical formula and hence we can definitely use Excel for the same. We have designed an Excel calculation sheet in which you have to simply insert the recorded data.
  5. What is the purpose of calculating Mean Kinetic Temperature (MKT)?
    We will consider medicine that requires storage between 2 to 8 Deg C. During storage and transportation of this medicine, it is possible that the surrounding temperature went above 8 Deg a number of times. Also, it is possible that the temperature went below 2 Deg C a few times. This can happen due to power outages, exposure to the sun during handling etc.
    Your data logger records this data including the duration of such variations. Now you need to calculate whether the variations caused damage to the medicine or whether its efficacy is compromised. The manufacturer of the medicine specifies that the medicine is safe to use if the MKT of the entire duration is between 2 to 8 Deg C. Thus if the MKT is between 2 to 8 Deg for the duration, this means that the medicine is safe to use in spite of the temperature variations.
    So if the medicine is shipped from the UK to Japan, the consignee at Japan will check the MKT of the entire duration before accepting the consignment. (This article is to be used only as a guideline. Please check with the medicine manufacturer for exact requirements.)
Revision history of this Excel calculation tool:
  1. Rev 0, 17-Feb-2014: Original version
  2. Rev 1, 13-Oct-2014: Separate sheet added for Fahrenheit & published online
  3. Rev 2, 23-Feb-2015: ΔH & R defined in the first two sheets
  4. Rev 3, 09-Mar-2015: Formatting changes only
  5. Rev 4, 15-May-2015: Formatting changes only
  6. Rev 5, 25-Jun-2015: Formatting changes only
  7. Rev 6, 25-Sep-2017: Major changes for increased functionality. The full & free version now made available online.
  8. Rev 7, 06-Feb-2018: Formatting changes only

online-store-for-data-loggers-by-VackerGlobal
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.

Posted in Mean Kinetic Temperature, Mean Kinetic Value, Temperature Mapping, Temperature Mapping & Validation, temperature qualification Tagged with: , , ,
111 comments on “How to calculate Mean Kinetic Temperature (MKT) in Excel Sheet (Free Download)
  1. Jon Pérez says:

    As a biomedical engineer, is very important to have a knowledge of how the temperature affects to the products, and this is an important tool.

  2. looking for a spreadsheet

  3. dafds says:

    looking for a spreadsheet to calculate mkt

  4. santiago says:

    Thank you

  5. dulce rebeca chavez aldana says:

    Free?

  6. Oscar says:

    Muchas gracias por el archivo

  7. Victor Rascón says:

    i like Excel… lol

  8. Ana says:

    thank you in advance

  9. ANCIZAR HERNANDEZ says:

    Thanks

  10. Mostafa says:

    The excel sheet is protected and i cant insert a lot of data on the excel sheet pleas can you help?

  11. S Gopani says:

    how do you calculate MKT for multiple set of readings within specified period, but without equal interval between those records….?

  12. Ramesh says:

    Please give the clarity about Delta H value

    How you are taking the Delta H as 83.144

    Is it constant or any calculation is there

  13. Anonymous says:

    Hi. Your units don’t match. Either EA needs to be in J/mol or R needs to be in KJ/mol*K. Other than that, great explanation of the concept. Very helpful.

  14. Anonymous says:

    Hi. Your units don’t match. Either EA needs to be changed to J/mol or R needs to be changed to KJ/mol*K. Other than that, great explanation of the concept. Very useful.

  15. Kai says:

    I have download the Excel sheet it is a great, however, I amcan advise not able to insert more row. Hope you can help.

  16. Catherine Artzt says:

    Thank you SO much for this spreadsheet. It saved my report!
    Do you have a spreadsheet for Mean Kinetic Relative Humidity?
    Much respect to you for making this, you are very smart!

  17. choukri says:

    je demande une feuille Excel pour le calcul de MKT

  18. Pravin Morajkar says:

    Excellent detailed explanation about MKT
    Thanks you very much, it helped me more.

  19. Luis Sandoval says:

    Thank you

  20. Ricardo Aguirre says:

    I have downloaded the excel spreadsheet but it is protected and can not add more rows to do teh calculation
    Could you help me?

  21. Ricardo says:

    The excel sheet is protected and i cant insert a lot of data on the excel sheet pleas can you help?

  22. J. Guy says:

    Opened this to receive free spreadsheet download – will comment once I try it

  23. Alex says:

    Hi, I am not able to unlock the free download.
    I sign with google plus but nothing is happening..
    Could you please help?

    thanks in advance

  24. Marco says:

    Excelente !!

  25. Dilya says:

    Hi.
    Please, send me MKT calculation free Excel Sheet.

    Thank you.

  26. Paola says:

    Por favor cómo puedo descargarme el excel ??

  27. wonder if you have native version of spreadsheet to add more rows of data.

  28. Mark says:

    Would greatly appreciate being sent the excel MKT spreadsheet

  29. Anonymous says:

    Thank you so much for your free Excel spreadsheet! We were able to use it to determine if our pharmaceutical inventory storage spaces were affected by the Northern California fires.

  30. Leonard Aska says:

    I have been trying to download the Excel worksheet to calculate MKT. I keep getting a link to the same page and have requested the spreadsheet many times without success. can anyone help?

  31. Poreto says:

    Thank you for sharing clear steps of MKT calculation. It helped me alot.

  32. Juliana says:

    If someone is following the written instructions to cross-check the calculation, the mathematical terms are wrong.

    numerator = upper part of the fraction
    denominator = lower part of the fraction

  33. Ati says:

    May I get a copy of the spreadsheet, don’t seem to work when downloading it. Many thanks.

  34. excel file do not arrive!

  35. Vacker says:

    Hi Fernando de la Rosa,

    In order to receive the spreadsheet, please fill in the form on the same page (https://www.temperaturemonitoringuae.com/how-to-calculate-mean-kinetic-temperature-mkt-in-excel/#signup), following which an email would be sent to you containing the download link.

  36. De Schrijver says:

    Hi,

    Could you please sent me the excel MKT spreadsheet ?
    Thank you in advance

    Céline De Schrijver

  37. Sidhu S says:

    I have been done the rough calculation of MKT and observed value is 5.586. I am looking for, how can I correlate with this MKT value to temperature excursions. Refrigerator temperature is 2- 8°C and some times it went upside upto 12°C. It may happen during operation time or some other conditions. Excursion is actually a deviation and how can I address this excursions in SOP and avoid deviation.

    • Vacker says:

      Your query is not clear. However, I will try to answer. Suppose you kept a medicine yesterday morning at 10.00 am and are checking taking it out today morning at 10.00 am. The refrigerators might have experienced few excursions during this 24 hours. Assuming that you have a data logger with data recording for this 24 hours, you will find out the MKT for this 24 hours. If the MKT value is within 2 to 8, then the medicine is acceptable. (This example is to explain the usage of MKT, without any commitments from our side whatsoever)

  38. Aaditya says:

    Can i apply the same calculation for medicines and vaccines?

  39. Pam Mason says:

    I have just downloaded the MKT excel sheet. But I’m not able to add additional rows to input more temperature set points.

  40. Ath says:

    Thanks for the tool.
    Please confirm that a high MKT value is related to a harsher temperature exposure in comparison with a low MKT value

  41. John Oleis says:

    Hello, please send me a copy of the Mean Kinetic Temperature in Excel Sheet.

  42. Vijay Jadeja says:

    Send free MKT calculation Excel sheet

  43. Sanjay says:

    I have a query,
    If temperature limit is 20-25C and after calculation of MKT, value comes 22.44 and average MKT value is 22.42.
    Now it is deviation of 0.02C from average temperature.
    My question is : is there any limit or range for MKT value ?

    Pls respond me at- sanjudivakar.15@gmail.com

  44. JTB says:

    Hello, how have you determined your delH value? Above you state it is constant, but isn’t that really dependent upon the material?

  45. Zaffir Iqbal says:

    Is the MKT software in excel a validated spreadsheet?

  46. Mike Stocks says:

    id like a copy of the spreadsheet

  47. Sandeep says:

    Need a working copy of the excelsheet for MKT calculation. Can you please share it.

  48. NYIRIMANZI Joseph Desire says:

    Thank you very much for the knowledge sharing. The MKT can be used in the past? this means can analysis temperature captured retrospectively to ensure whether the products used was safe?
    Looking forward to hearing from you.

  49. Russell Gibbons says:

    Thank you for this full explanation of MKT. I have tried to download your very helpful spreadsheet from the links on this page, but can’t get a result. Could you please send me a copy by email?

    Many thanks

  50. I have a problem, when I use the spreadsheet, I cant insert or delete rows for the correct calculation of MKT, could you support me with this problem

  51. WILLIAM CHANHUALLA CASTRO says:

    HOLA LLEVO CUATRO DIAS ESPERANDO EL ENLACE DEL EXEL MKT REALICE EL PAGO Y HASTA AHORA NADA

    HELLO I HAVE TAKEN FOUR DAYS WAITING FOR THE LINK OF EXCEL MKT, MAKE THE PAYMENT AND UNTIL NOW NOTHING

  52. Chetan says:

    How do we get the value of Delta H? How did you get 83.144?

  53. Thaer Jomhawi says:

    I couldn’t proceed to pay please contact me

  54. Dilip Kumar P.M says:

    Will you able to provide the validation documentation of the excel spreadsheet

  55. Dieu Merci says:

    C’est vraiment intéressant, si vous pouvez me partager le mode de calcul de la température cinétique moyenne par mail, ça nous aidera, nous qui somme dans les pays en voie de développement à améliorer notre chaine d’approvisionnement.

  56. Utkarsh says:

    Please explain Step 7.

  57. Albern says:

    The link to purchase the full version is not working. I would like to purchase the full version.

Leave a Reply

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

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.