custom function spreadsheet fix
categories:
Spreadsheet
This is an update to all the existing Excel custom functions so far.
The [Frankenstein spreadsheet](/monster-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](https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f).
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](https://support.microsoft.com/en-us/office/find-help-on-using-the-visual-basic-editor-61404b99-84af-4aa3-b1ca-465bc4f45432)
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](https://stackoverflow.com/questions/4262421/how-to-put-a-tooltip-on-a-user-defined-function)
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.](https://pubmed.ncbi.nlm.nih.gov/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!