custom function spreadsheet fix

Feb 8, 2024 • Mark Morgan

This is an update to all the existing Excel custom functions so far.

The Frankenstein spreadsheet has multiple custom functions.

As I mentioned in a previous post, there is more information about Excel custom functions at this Microsoft support page.

To make a custom function you’ll need to open the visual basic editor in your excel program and then enter and save the code. More information about doing this is at this Microsoft support page

And again, standard Excel functions will guide you as you try to select the cells for the calculation. The bummer about custom Excel functions is that there is no automatically displaying guide. You can make one appear (in Windows) by pressing CTRL-A. More on this is at this stackoverflow page

The most significant change of these formulas is that I have remove the message box pop up warnings and just set the function equal to the error message. It eliminates a flurry of message box pop ups when you open the spreadsheet.

Corrected calcium

This uses albumin to correct the calcium level.

Function CALCIUMCORRECTED(calcium, albumin)
    If IsEmpty(calcium) Then
        CALCIUMCORRECTED = "error:  calcium field is empty"
        GoTo LineZilch
    ElseIf calcium < 0 Then
        CALCIUMCORRECTED = "error:  calcium cannot be 0 or less"
        GoTo LineZilch
    End If
    If IsEmpty(albumin) Then
        CALCIUMCORRECTED = "error:  albumin field is empty"
        GoTo LineZilch
    ElseIf albumin < 0 Then
        CALCIUMCORRECTED = "albumin cannot be 0 or less"
        GoTo LineZilch
    End If
    
    CALCIUMCORRECTED = calcium + (0.8 * (4 - albumin))
    CALCIUMCORRECTED = Application.Round(CALCIUMCORRECTED, 2)
    
LineZilch:
'nothing happens here
End Function

AST to Platelet Ratio Index (APRI)

This is used to estimate liver fibrosis.

Function LIVERAPRI(ast, platelets)
    If IsEmpty(ast) Then
        LIVERAPRI = "error:  ast field is empty"
        GoTo LineZilch
    ElseIf ast < 0 Then
        LIVERAPRI = "error:  ast cannot be 0 or less"
        GoTo LineZilch
    End If
    If IsEmpty(platelets) Then
        LIVERAPRI = "error:  platelets field is empty"
        GoTo LineZilch
    ElseIf platelets < 0 Then
        LIVERAPRI = "error:  platelets cannot be 0 or less"
        GoTo LineZilch
    End If
    
    LIVERAPRI = (100 * ((ast / 37) / platelets))
    LIVERAPRI = Application.Round(LIVERAPRI, 2)
    
LineZilch:
'nothing happens here
End Function

The function below is used to interpret the APRI score.

Function LIVERAPRIINTERPRETATION(apri)
    If IsEmpty(apri) Then
        LIVERAPRIINTERPRETATION = "error:  apri field is empty"
        GoTo LineZilch
    ElseIf apri < 0 Then
        LIVERAPRIINTERPRETATION = "error:  apri cannot be 0 or less"
        GoTo LineZilch
    End If
    If apri > 2 Then
        GoTo Line1
    ElseIf apri > 1.5 Then
        GoTo Line2
    ElseIf apri > 0.5 Then
        GoTo Line3
    Else
        GoTo Line4
    End If
Line1:
    LIVERAPRIINTERPRETATION = "Likely cirrhosis"
    GoTo LineZilch
Line2:
    LIVERAPRIINTERPRETATION = "Likely significant fibrosis, cirrhosis possible"
    GoTo LineZilch
Line3:
    LIVERAPRIINTERPRETATION = "Significant fibrosis or cirrhosis possible"
    GoTo LineZilch
Line4:
    LIVERAPRIINTERPRETATION = "Unlikely cirrhosis or significant fibrosis"
    GoTo LineZilch
LineZilch:
'nothing happens here
End Function
Function BMIIMPERIAL(heightinches, weightpounds)
    BMIIMPERIAL = 703 * weightpounds / heightinches ^ 2
    BMIIMPERIAL = Application.Round(BMIIMPERIAL, 2)
End Function

The Alcoholic Liver Disease/Nonalcoholic Fatty Liver Disease Index (ANI)

This is used to distinguish alcoholic liver disease from fatty liver disease.

Function LIVERANI(gender, mcv, ast, alt, heightinches, weightpounds)
' reference https://www.ncbi.nlm.nih.gov/pmc/articles/PMC2483536/
' ani = -58.5 + 0.637 (MCV) + 3.91 (AST/ALT) – 0.406 (BMI) + 6.35 for male gender.
' bmi = weight (lb) / [height (in)]2 x 703
    If LCase(gender) = "male" Then
        GoTo Line1
    ElseIf LCase(gender) = "female" Then
        GoTo Line2
    End If

Line1:
    gender = 6.35
    GoTo Line3
Line2:
    gender = 0
    GoTo Line3
Line3:
    LIVERANI = -58.5 + (0.637 * mcv) + (3.91 * ast / alt) - (0.406 * 703 * weightpounds / heightinches ^ 2) + gender
    LIVERANI = Application.Round(LIVERANI, 2)
LineZilch:
'nothing happens here
End Function

The function below interprets the ANI score with a probability of alcoholic liver disease.

Function LIVERANIPROBABILITY(ani)
    LIVERANIPROBABILITY = (Exp(ani)) / (1 + (Exp(ani)))
    LIVERANIPROBABILITY = Application.Round(LIVERANIPROBABILITY, 2)
End Function

ASCVD Risk Calculators

This is an updated 10-year ASCVD Risk formula. As with all the others here, I just removed the message box warnings that were in the prior versions.

Function TENYEARASCVDRISK(gender, age, race, tobacco, diabetes, hypertension_medication, total_cholesterol, hdl_cholesterol, systolic_blood_pressure)

    If LCase(gender) = "male" Or LCase(gender) = "female" Then
        GoTo Line1
    Else
        TENYEARASCVDRISK = "error:  gender needs to be Male or Female for this calculator"
        GoTo LineZilch
    End If

Line1:
    If age >= 40 And age <= 75 Then
        GoTo Line2
    Else
        TENYEARASCVDRISK = "error:  age needs to be between 40 and 75"
        GoTo LineZilch
    End If

Line2:
    If LCase(race) = "white" Or LCase(race) = "african american" Then
        GoTo Line3
    Else
        TENYEARASCVDRISK = "error:  race needs to be either White or African American for this calculator"
        GoTo LineZilch
    End If

Line3:
    If LCase(tobacco) = "yes" Or LCase(tobacco) = "no" Then
        GoTo Line4
    Else
        TENYEARASCVDRISK = "error:  tobacco needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If
    
Line4:
    If LCase(tobacco) = "yes" Then
        tobacco = 1
    ElseIf LCase(tobacco) = "no" Then
        tobacco = 0
    End If

    If LCase(diabetes) = "yes" Or LCase(diabetes) = "no" Then
        GoTo Line5
    Else
        TENYEARASCVDRISK = "error:  diabetes needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If
    
Line5:
    If LCase(diabetes) = "yes" Then
        diabetes = 1
    ElseIf LCase(diabetes) = "no" Then
        diabetes = 0
    End If
    
    If LCase(hypertension_medication) = "yes" Or LCase(hypertension_medication) = "no" Then
        GoTo Line6
    Else
        TENYEARASCVDRISK = "error:  hypertension_medication needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If

Line6:
    If total_cholesterol > 0 Then
        GoTo Line7
    Else
        TENYEARASCVDRISK = "error:  total_cholesterol needs to be above 0"
        GoTo LineZilch
    End If
    
Line7:
    If hdl_cholesterol > 0 Then
        GoTo Line8
    Else
        TENYEARASCVDRISK = "error:  hdl_cholesterol needs to be above 0"
        GoTo LineZilch
    End If
    
Line8:
    If systolic_blood_pressure > 0 Then
        GoTo Line9
    Else
        TENYEARASCVDRISK = "error:  systolic_blood_pressure needs to be above 0"
        GoTo LineZilch
    End If

Line9:
    If LCase(gender) = "female" And LCase(race) = "white" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.9665) ^ Exp(((-29.799 * Log(age)) + (4.884 * (Log(age)) ^ 2) + (13.54 * (Log(total_cholesterol))) + (-3.114 * Log(age) * Log(total_cholesterol)) + (-13.578 * Log(hdl_cholesterol)) + (3.149 * Log(age) * Log(hdl_cholesterol)) + (1.957 * Log(systolic_blood_pressure)) + (7.574 * tobacco) + (-1.665 * Log(age) * tobacco) + (0.661 * diabetes)) + 29.18)))
    
    ElseIf LCase(gender) = "female" And LCase(race) = "white" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.9665) ^ Exp(((-29.799 * Log(age)) + (4.884 * (Log(age)) ^ 2) + (13.54 * Log(total_cholesterol)) + (-3.114 * Log(age) * Log(total_cholesterol)) + (-13.578 * Log(hdl_cholesterol)) + (3.149 * Log(age) * Log(hdl_cholesterol)) + (2.019 * Log(systolic_blood_pressure)) + (7.574 * tobacco) + (-1.665 * Log(age) * tobacco) + (0.661 * diabetes)) + 29.18)))

    ElseIf LCase(gender) = "female" And LCase(race) = "african american" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.9533) ^ Exp(((17.114 * Log(age)) + (0.94 * Log(total_cholesterol)) + (-18.92 * Log(hdl_cholesterol)) + (4.475 * Log(age) * Log(hdl_cholesterol)) + (27.82 * Log(systolic_blood_pressure)) + (-6.087 * Log(age) * Log(systolic_blood_pressure)) + (0.691 * tobacco) + (0.874 * diabetes)) - 86.61)))
    
    ElseIf LCase(gender) = "female" And LCase(race) = "african american" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.9533) ^ Exp(((17.114 * Log(age)) + (0.94 * Log(total_cholesterol)) + (-18.92 * Log(hdl_cholesterol)) + (4.475 * Log(age) * Log(hdl_cholesterol)) + (29.291 * Log(systolic_blood_pressure)) + (-6.432 * Log(age) * Log(systolic_blood_pressure)) + (0.691 * tobacco) + (0.874 * diabetes)) - 86.61)))
        
    ElseIf LCase(gender) = "male" And LCase(race) = "white" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.9144) ^ Exp(((12.344 * Log(age)) + (11.853 * Log(total_cholesterol)) + (-2.664 * Log(age) * Log(total_cholesterol)) + (-7.99 * Log(hdl_cholesterol)) + (1.769 * Log(age) * Log(hdl_cholesterol)) + (1.764 * Log(systolic_blood_pressure)) + (7.837 * tobacco) + (-1.795 * Log(age) * tobacco) + (0.658 * diabetes)) - 61.18)))
        
    ElseIf LCase(gender) = "male" And LCase(race) = "white" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.9144) ^ Exp(((12.344 * Log(age)) + (11.853 * Log(total_cholesterol)) + (-2.664 * Log(age) * Log(total_cholesterol)) + (-7.99 * Log(hdl_cholesterol)) + (1.769 * Log(age) * Log(hdl_cholesterol)) + (1.797 * Log(systolic_blood_pressure)) + (7.837 * tobacco) + (-1.795 * Log(age) * tobacco) + (0.658 * diabetes)) - 61.18)))

    ElseIf LCase(gender) = "male" And LCase(race) = "african american" And LCase(hypertension_medication) = "no" Then
        TENYEARASCVDRISK = (1 - ((0.8954) ^ Exp(((2.469 * Log(age)) + (0.302 * Log(total_cholesterol)) + (-0.307 * Log(hdl_cholesterol)) + (1.809 * Log(systolic_blood_pressure)) + (0.549 * tobacco) + (0.645 * diabetes)) - 19.54)))
            
    ElseIf LCase(gender) = "male" And LCase(race) = "african american" And LCase(hypertension_medication) = "yes" Then
        TENYEARASCVDRISK = (1 - ((0.8954) ^ Exp(((2.469 * Log(age)) + (0.302 * Log(total_cholesterol)) + (-0.307 * Log(hdl_cholesterol)) + (1.916 * Log(systolic_blood_pressure)) + (0.549 * tobacco) + (0.645 * diabetes)) - 19.54)))
    End If
LineZilch:
'nothing happens here

End Function

This is the lifetime ASCVD risk calculator without the message box warnings.

Function LIFETIMEASCVDRISK(gender, age, tobacco, diabetes, total_cholesterol, systolic_blood_pressure)

    If LCase(gender) = "male" Or LCase(gender) = "female" Then
        GoTo Line1
    Else
        LIFETIMEASCVDRISK = "error:  gender needs to be Male or Female for this calculator"
        GoTo LineZilch
    End If

Line1:

    If LCase(gender) = "male" Then
        gender = 1000
    ElseIf LCase(gender) = "female" Then
        gender = 0
    End If
    
    If age >= 20 And age <= 59 Then
        GoTo Line2
    Else
        LIFETIMEASCVDRISK = "error:  age needs to be between 20 and 59"
        GoTo LineZilch
    End If

Line2:
    If LCase(tobacco) = "yes" Or LCase(tobacco) = "no" Then
        GoTo Line3
    Else
        LIFETIMEASCVDRISK = "error:  tobacco needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If
    
Line3:
    If LCase(tobacco) = "yes" Then
        tobacco = 100
    ElseIf LCase(tobacco) = "no" Then
        tobacco = 0
    End If

    If LCase(diabetes) = "yes" Or LCase(diabetes) = "no" Then
        GoTo Line4
    Else
        LIFETIMEASCVDRISK = "error:  diabetes needs to be either Yes or No for this calculator"
        GoTo LineZilch
    End If
    
Line4:
    If LCase(diabetes) = "yes" Then
        diabetes = 100
    ElseIf LCase(diabetes) = "no" Then
        diabetes = 0
    End If

    If total_cholesterol > 0 Then
        GoTo Line5
    Else
        LIFETIMEASCVDRISK = "error:  total_cholesterol needs to be above 0"
        GoTo LineZilch
    End If
    
Line5:
    If total_cholesterol >= 240 Then
        GoTo Line6
    ElseIf total_cholesterol >= 200 Then
        GoTo Line7
    ElseIf total_cholesterol >= 180 Then
        GoTo Line8
    Else
        GoTo Line9
    End If
    
Line6:
    total_cholesterol = 100
    GoTo Line10

Line7:
    total_cholesterol = 10
    GoTo Line10

Line8:
    total_cholesterol = 1
    GoTo Line10

Line9:
    total_cholesterol = 0
    GoTo Line10
    
Line10:
    If systolic_blood_pressure > 0 Then
        GoTo Line11
    Else
        LIFETIMEASCVDRISK = "error:  systolic_blood_pressure needs to be above 0"
        GoTo LineZilch
    End If

Line11:
    If systolic_blood_pressure >= 160 Then
        GoTo Line12
    ElseIf systolic_blood_pressure >= 140 Then
        GoTo Line13
    ElseIf systolic_blood_pressure >= 120 Then
        GoTo Line14
    Else
        GoTo Line15
    End If
    
Line12:
    systolic_blood_pressure = 100
    GoTo Line16

Line13:
    systolic_blood_pressure = 10
    GoTo Line16
    
Line14:
    systolic_blood_pressure = 1
    GoTo Line16
    
Line15:
    systolic_blood_pressure = 0
    GoTo Line16
    
Line16:
    If (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1199 Then
        LIFETIMEASCVDRISK = "2 or more MAJOR risk factors - 68.9%"
    
    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1099 Then
        LIFETIMEASCVDRISK = "1 MAJOR risk factor - 50.4%"
    
    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1009 Then
        LIFETIMEASCVDRISK = "1 or more ELEVATED risk factors - 45.5%"

    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 1000 Then
        LIFETIMEASCVDRISK = "1 or more NOT OPTIMAL risk factors - 36.4%"
    
    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 999 Then
        LIFETIMEASCVDRISK = "All OPTIMAL risk factors - 5.2%"

    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 199 Then
        LIFETIMEASCVDRISK = "2 or more MAJOR risk factors - 50.2%"
    
    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 99 Then
        LIFETIMEASCVDRISK = "1 MAJOR risk factor - 38.8%"
    
    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 9 Then
        LIFETIMEASCVDRISK = "1 or more ELEVATED risk factors - 39.1%"

    ElseIf (systolic_blood_pressure + total_cholesterol + tobacco + diabetes + gender) > 0 Then
        LIFETIMEASCVDRISK = "1 or more NOT OPTIMAL risk factors - 26.9%"
    Else
        LIFETIMEASCVDRISK = "All OPTIMAL risk factors - 8.2%"
    End If

LineZilch:
'nothing happens here

End Function

Basal Metabolic Rate Calculator

This is a new calculation that uses the Schofield equation to calculate basal metabolic rate. Here are the references:

Schofield WN. Predicting basal metabolic rate, new standards and review of previous work. Hum Nutr Clin Nutr. 1985;39 Suppl 1:5-41. PMID: 4044297.

Function SCHOFIELDBMR(gender, age, weight)

    If LCase(gender) = "male" Or LCase(gender) = "female" Then
        GoTo Line1
    Else
        SCHOFIELDBMR = "error:  gender needs to be Male or Female for this calculator"
        GoTo LineZilch
    End If

Line1:

    If LCase(gender) = "male" Or LCase(gender) = "female" Then
        GoTo Line2
    Else
        SCHOFIELDBMR = "error:  gender needs to be male or female"
    End If
    
    
Line2:
    
    If age >= 18 And age <= 120 Then
        GoTo Line3
    Else
        SCHOFIELDBMR = "error:  age needs to be between 18 and 120 for this calculator"
        GoTo LineZilch
    End If

Line3:
    If age < 30 And LCase(gender) = "male" Then
        SCHOFIELDBMR = ((0.063 * weight / 2.2) + 2.896) * 240 * 1.3
    ElseIf age < 60 And LCase(gender) = "male" Then
        SCHOFIELDBMR = ((0.048 * weight / 2.2) + 3.653) * 240 * 1.3
    ElseIf age <= 120 And LCase(gender) = "male" Then
        SCHOFIELDBMR = ((0.049 * weight / 2.2) + 2.459) * 240 * 1.3
    ElseIf age < 30 And LCase(gender) = "female" Then
        SCHOFIEDLBMR = ((0.062 * weight / 2.2) + 2.036) * 240 * 1.3
    ElseIf age < 60 And LCase(gender) = "female" Then
        SCHOFIEDLBMR = ((0.034 * weight / 2.2) + 3.538) * 240 * 1.3
    ElseIf age <= 120 And LCase(gender) = "female" Then
        SCHOFIEDLBMR = ((0.038 * weight / 2.2) + 2.755) * 240 * 1.3
    End If

LineZilch:
'nothing happens here

End Function

Thanks for reading!