Last Modified: 31 Aug 2014 Psychrometrics and Related Calculations Collection of Macro Functions for Excel(R) Thomas E. Bernard College of Public Health University of South Florida Tampa FL 33612-3805 tbernard@health.usf.edu personal.health.usf.edu/tbernard These Excel macro functions are designed to compute water vapor pressure (PVC, PVRH, PVSAT), relative humidity (RHCDP, RHCWB, RHDPF, RHWBF), psychrometric wet bulb temperature (PWBC), natural wet bulb temperature (NWB) and US NWS Heat Index (HIC, HIF, HICF). The Visual Basic code for each function is provided below. The relationships except Heat Index are described in T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 In Excel, open the Visual Basic Editor at Tools => Macros => Visual Basic Editor. Create a module with Insert => Module. Copy the selected functions or all of the functions into the VBA module. The Module can be named if desired. Once available in Excel, a macro function is used like an Excel built-in function, which returns a value based on the referenced input cells. They can be seen in the Function List under User Defined. The macro(s) will be lost if it is saved in a default format (xls, xlsx); save the workbook as an xlsm file to save the macros with the Excel workbook. Unless noted otherwise, the units are metric (C, kPa, m/s) The macros follow. Either copy everything below the line into the Visual Basic module or any selection(s) that start with Function and end with End Function. ---------------------------------- Function PVC(T, PWB) ' Water Vapor Pressure Function from Dry Bulb and Psychrometric Wet Bulb Temperatures -- C, kPa ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = dry bulb temperature C ' PWB = psychrometric wet bulb temperature C ' Returns a value for Pv (water vapor pressure) in kPa TDB = T TPWB = PWB PVA = 0.6105 * Exp(17.27 * TPWB / (TPWB + 237.3)) - 0.067 * (TDB - TPWB) PVC = PVA End Function Function PVRH(T, RH) ' Water Vapor Pressure Function from Dry Bulb and relative humidity -- C, % relative humidity, kPa ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = dry bulb temperature C ' RH = % relative humidity ' Returns a value for Pv (water vapor pressure) in kPa TDB = T RHP = RH PVA = (0.6105 * Exp(17.27 * TDB / (TDB + 237.3))) * RHP / 100 PVRH = PVA End Function Function PVSAT(T) ' Saturated Water Vapor Pressure Function at a given dry bulb (or dew point) temperature -- C, kPa ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = temperature ¡C ' Returns a value for saturated water vapor pressure at the temperature. If temperature is dew point, then the Pv is for the ambient environment. TEMP = T PVA = 0.6105 * Exp(17.27 * TEMP / (TEMP + 237.3)) PVSAT = PVA End Function Function RHDPC(T, DP) ' Relative Humidity Function from dry bulb and dew point temperatures -- C ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = dry bulb temperature C ' PWB = psychrometric wet bulb temperature C ' Returns a value for RH (relative humidity) in % TDB = T TDP = DP PVA = 0.6105 * Exp(17.27 * TDP / (TDP + 237.3)) PVS = 0.6105 * Exp(17.27 * TDB / (TDB + 237.3)) RHP = 100 * PVA / PVS RHDPC = RHP End Function Function RHWBC(T, PWB) ' Relative Humidity Function from dry bulb and psychometric wet bulb temperatures -- C ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = dry bulb temperature C ' PWB = psychrometric wet bulb temperature C ' Returns a value for RH (relative humidity) in % TDB = T TPWB = PWB PVA = 0.6105 * Exp(17.27 * TPWB / (TPWB + 237.3)) - 0.067 * (TDB - TPWB) PVS = 0.6105 * Exp(17.27 * TDB / (TDB + 237.3)) RHP = 100 * PVA / PVS RHWBC = RHP End Function Function RHDPF(T, DP) ' Relative Humidity Function from dry bulb and dew point temperatures -- ¡F ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = dry bulb temperature C ' PWB = psychrometric wet bulb temperature C ' Returns a value for RH (relative humidity) in % TDB = (T - 32) / 1.8 TDP = (DP - 32) / 1.8 PVA = 0.6105 * Exp(17.27 * TDP / (TDP + 237.3)) PVS = 0.6105 * Exp(17.27 * TDB / (TDB + 237.3)) RHP = 100 * PVA / PVS RHDPF = RHP End Function Function RHWBF(T, PWB) ' Relative Humidity Function from dry bulb and psychometric wet bulb temperatures -- F ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = dry bulb temperature F ' PWB = psychrometric wet bulb temperature F ' Returns a value for RH (relative humidity) in % TDB = (T - 32) / 1.8 TPWB = (PWB - 32) / 1.8 PVA = 0.6105 * Exp(17.27 * TPWB / (TPWB + 237.3)) - 0.067 * (TDB - TPWB) PVS = 0.6105 * Exp(17.27 * TDB / (TDB + 237.3)) RHP = 100 * PVA / PVS RHWBF = RHP End Function Function PWBC(T, PVA) ' Psychrometric Wet Bulb Function from dry bulb temperature and water vapor pressure -- C and kPA ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T = dry bulb temperature C ' PVA = ambient water vapor pressure kPA ' Returns a value for psychrometric wet bulb temperature in C TDB = T PWB = T PA = PVA PV1 = 0 ' Method is a simple stepwise interation For cnt = 1 To 1000 PV1 = 0.6105 * Exp(17.27 * PWB / (PWB + 237.3)) - 0.067 * (TDB - PWB) DELTAPV = PV1 - PA: 'Assumes that PV1 is greater than PA and slowly approaches PA with each iteration. If DELTAPV < 0.01 Then GoTo getout PWB = PWB - 0.05 Next cnt getout: 'Found a Value PWBC = PWB End Function Function NWB(DB, PWB, G, VAIR) ' Natural Wet Bulb Temperature Function from dry bulb, psychometric wet bulb and globe temperatures, and air speed -- C and m/s ' Version 1.1: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' T E Bernard and M Pourmoghani. Prediction of Workplace Wet Bulb Global Temperature. Applied Occupational and Environmental Hygiene 14:126-134, 1999 ' T E Bernard and R L Cross. Heat stress management: Case study in an aluminum smelter. International Journal of Industrial Ergonomics 23:609-620, 1999 ' DB = dry bulb temperature C ' PWB = psychrometric wet bulb temperature C ' G = globe temperature C ' VAIR = air speed m/s TDB = DB TPWB = PWB TG = G V = VAIR DELTADBG = TG - TDB If DELTADBG > 4 Then GoTo RadHeat ' Radiant heat effect is minimal with a difference between globe and dry bulb temperratures < 4 ¡C ADJV = 0.85 If V >= 3 Then ADJV = 1 If V < 3 And V > 0.03 Then ADJV = 0.96 + 0.069 * Log(V) / 2.303: ' Log function is the natural log and is converted to base 10 TNWB = TDB - ADJV * (TDB - TPWB) GoTo ReportOut RadHeat: ' Radiant heat is high enough to affect natural wet bulb temperature ADJV = 1.1 If V >= 1 Then ADJV = -0.1 If V < 1 And V > 0.1 Then ADJV = 0.1 / V ^ 1.1 - 0.2 TNWB = TPWB + 0.25 * DELTADBG + ADJV ReportOut: ' End of process to estimate Tnwb NWB = TNWB End Function Function HIC(T, RH) ' Heat Index Function -- C ' Version 2.0: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' Equation from NWS website on 31 AUG 2013 http://www.hpc.ncep.noaa.gov/html/heatindex_equation.shtml ' T = dry bulb temperature C ' RH = % relative humidity ' Returns a value for HI in C ' Not valid for dry bulb temperatures < 80 F (26.7 C) and >112 F (44.4 C) and for HI <80 (26.7 C) and > 137 (58.3 C), where HI is forced to -1 TF = 1.8 * T + 32 ADJ = 0 HICAL = -42.379 + 2.04901523 * TF + 10.14333127 * RH - 0.22475541 * TF * RH - 0.00683783 * TF * TF - 0.05481717 * RH * RH + 0.00122874 * TF * TF * RH + 0.00085282 * TF * RH * RH - 1.99e-06 * TF * TF * RH * RH If RH < 13 And TF > 80 And TF < 112 Then ADJ = -((13 - RH) / 4) * Sqr((17 - Abs(TF - 95#)) / 17) If RH > 85 And TF > 80 And TF < 87 Then ADJ = ((RH - 85) / 10) * ((87 - TF) / 5) HICAL = HICAL + ADJ If TF < 80 Or TF > 112 Or HICAL < 80 Or HICAL > 137 Then HICAL = -1 HICAL = (HICAL - 32) / 1.8 HIC = HICAL End Function Function HIF(T, RH) ' Heat Index Function -- F ' Version 2.0: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' Equation from NWS website on 31 AUG 2013 http://www.hpc.ncep.noaa.gov/html/heatindex_equation.shtml ' T = dry bulb temperature F ' RH = % relative humidity ' Returns a value for HI in F ' Not valid for dry bulb temperatures < 80 F (26.7 C) and >112 F (44.4 C) and for HI <80 (26.7 C) and > 137 (58.3 C), where HI is forced to -1 TF = T ADJ = 0 HICAL = -42.379 + 2.04901523 * TF + 10.14333127 * RH - 0.22475541 * TF * RH - 0.00683783 * TF * TF - 0.05481717 * RH * RH + 0.00122874 * TF * TF * RH + 0.00085282 * TF * RH * RH - 1.99e-06 * TF * TF * RH * RH If RH < 13 And TF > 80 And TF < 112 Then ADJ = -((13 - RH) / 4) * Sqr((17 - Abs(TF - 95#)) / 17) If RH > 85 And TF > 80 And TF < 87 Then ADJ = ((RH - 85) / 10) * ((87 - TF) / 5) HICAL = HICAL + ADJ If TF < 80 Or TF > 112 Or HICAL < 80 Or HICAL > 137 Then HICAL = -1 HIF = HICAL End Function Function HICF(T, RH) ' Heat Index Function -- ¡C in and ¡F out ' Version 1.0: 8/31/2014 ' Excel(R) Function written by Thomas E. Bernard (see personal.health.usf.edu/tbernard) ' (C) 2014 Thomas E. Bernard ' No warranty is proffered. ' Equation from NWS website on 31 AUG 2013 http://www.hpc.ncep.noaa.gov/html/heatindex_equation.shtml ' T = dry bulb temperature C ' RH = % relative humidity ' Returns a value for HI in C ' Not valid for dry bulb temperatures < 80 F (26.7 C) and >112 F (44.4 C) and for HI <80 (26.7 C) and > 137 (58.3 C), where HI is forced to -1 TF = 1.8 * T + 32 ADJ = 0 HICAL = -42.379 + 2.04901523 * TF + 10.14333127 * RH - 0.22475541 * TF * RH - 0.00683783 * TF * TF - 0.05481717 * RH * RH + 0.00122874 * TF * TF * RH + 0.00085282 * TF * RH * RH - 1.99e-06 * TF * TF * RH * RH If RH < 13 And TF > 80 And TF < 112 Then ADJ = -((13 - RH) / 4) * Sqr((17 - Abs(TF - 95#)) / 17) If RH > 85 And TF > 80 And TF < 87 Then ADJ = ((RH - 85) / 10) * ((87 - TF) / 5) HICAL = HICAL + ADJ If TF < 80 Or TF > 112 Or HICAL < 80 Or HICAL > 137 Then HICAL = -1 HICF = HICAL End Function