Correction Algorithms

Components
#1

Hi Everyone
Has anyone worked out the Excel formulas for the Correction Algorithms?
I have 18 months worth of data to correct on multiple monitors.
I would like to know that my correction is accurate.
Kind regards Paolo

#2

Which ones are you wanting to apply? The temp/humidity correction for the outdoor sensors is pretty straight forward and easy to do in Excel.

The PM2.5 correction gets much more complex, and while I was able to do it in Excel, I only used it when testing changes in the firmware and didn’t save them. Just substituted the references to sensor readings to the proper Excel cell, but things like squaring the values or doing all of it as nested IF statements can get long and complex

#3

In Python, I implemented following functions for each correction algorithm:

def correct_pm(x, RH):
    # https://www.airgradient.com/documentation/correction-algorithms/

    if ((x >= 0) and (x < 30)):
        epa_pm = (0.524 * x) - (0.0862 * RH) + 5.75

    elif (x < 50):
        epa_pm = (
          ((0.786 * (x/20 - 3./2)) + (0.524 * (1 - (x/20 - 3./2)))) * x
          - (0.0862 * RH) + 5.75
        )

    elif (x < 210):
        epa_pm= (0.786 * x) - (0.0862 * RH) + 5.75

    elif (x < 260):
        epa_pm = (
          ((0.69 * (x/50 - 21./5)) + (0.786 * (1 - (x/50 - 21./5)))) * x
          - (0.0862 * RH * (1 - (x/50 - 21./5)))
          + (2.966 * (x/50 - 21./5))
          + (5.75 * (1 - (x/50 - 21./5)))
          + (8.84 * pow(10, -4) * pow(x, 2) * (x/50 - 21./5))
        )

    else:
        epa_pm = 2.966 + (0.69 * x) + (pow(8.8410, -4) * pow(x, 2))
    
    return epa_pm

def correct_temp(x):
    # https://www.airgradient.com/documents/webinars/AirGradient_Webinar_Temperature_and_RH_Correction_MAR2024.pdf
    # https://www.airgradient.com/documentation/correction-algorithms/
    if x < 10:
        return x*1.327 - 6.738
    else:
        return x*1.181 - 5.112

def correct_rh(x):
    # https://www.airgradient.com/documents/webinars/AirGradient_Webinar_Temperature_and_RH_Correction_MAR2024.pdf
    # https://www.airgradient.com/documentation/correction-algorithms/
    return x*1.259 + 7.34
#4

Then I asked ChatGPT to convert these Python functions into Excel-compatible formulas. Here are the results (I tested with a few values I had already computed in Python, and seems consistent):

Formula for PM correction, where “A1” cell represents PM2.5 ; and “B1” cell is relative humidity

=IF(AND(A1 >= 0, A1 < 30), (0.524 * A1) - (0.0862 * B1) + 5.75, 
IF(AND(A1 >= 30, A1 < 50), ((0.786 * (A1 / 20 - 3 / 2)) + (0.524 * (1 - (A1 / 20 - 3 / 2)))) * A1 - (0.0862 * B1) + 5.75, 
IF(AND(A1 >= 50, A1 < 210), (0.786 * A1) - (0.0862 * B1) + 5.75, 
IF(AND(A1 >= 210, A1 < 260), ((0.69 * (A1 / 50 - 21 / 5)) + (0.786 * (1 - (A1 / 50 - 21 / 5)))) * A1 - (0.0862 * B1 * (1 - (A1 / 50 - 21 / 5))) + (2.966 * (A1 / 50 - 21 / 5)) + (5.75 * (1 - (A1 / 50 - 21 / 5))) + (8.84 * 10^-4 * A1^2 * (A1 / 50 - 21 / 5)), 
2.966 + (0.69 * A1) + (8.8410^-4 * A1^2))))

Temperature correction (A1 = temperature):

=IF(A1 < 10, A1 * 1.327 - 6.738, A1 * 1.181 - 5.112)

Relative Humidity correction (A1 = RH):

=A1 * 1.259 + 7.34
#5

Hi Guilherme

Thank you for your help. Great idea to work it out in python then ChatGPT.

This third verification of the formula gives me the confidence to start correcting the 18 months of data we have.

A colleague and had worked out the formula written slightly differently but essentially the same.

The ChatGPT made one error in the last part of the formula. Corrected below

=IF(AND(RC10 >= 0, RC10 < 30), (0.524 * RC10) - (0.0862 * RC30) + 5.75, IF(AND(RC10 >= 30, RC10 < 50), ((0.786 * (RC10 / 20 - 3 / 2)) + (0.524 * (1 - (RC10 / 20 - 3 / 2)))) * RC10 - (0.0862 * RC30) + 5.75,
IF(AND(RC10 >= 50, RC10 < 210), (0.786 * RC10) - (0.0862 * RC30) + 5.75, IF(AND(RC10 >= 210, RC10 < 260), ((0.69 * (RC10 / 50 - 21 / 5)) + (0.786 * (1 - (RC10 / 50 - 21 / 5)))) * RC10 - (0.0862 * RC30 * (1 - (RC10 / 50 - 21 / 5))) + (2.966 * (RC10 / 50 - 21 / 5)) + (5.75 * (1 - (RC10 / 50 - 21 / 5))) + (8.84 * 10^-4 * RC10^2 * (RC10 / 50 - 21 / 5)), 2.966 + (0.69 * RC10) + (8.84*10^-4 * RC10^2))))

Thanks again.

Kind regards

Paolo

1 Like
#6

Hi Paolo, so it was missing that multiplication sign between 8.84 and 10^-4, right? Great catch! I wonder now if I had a paid subscription this error would not have happened.

Having 18 months of data sounds quite exciting. Have fun with the analysis :wink:

1 Like