lifetime atherosclerotic cardiovascular disease (ASCVD) risk calculator spreadsheet custom function
categories:
Spreadsheet
Cardiovascular
Continuing on with ASCVD risk calculators. This is the lifetime atherosclerotic cardiovascular disease (ASCVD) risk calculator.
The [Frankenstein spreadsheet](/frankenstein) had this originally as a cell-based calculation, but it seems better to share Visual Basic for Applications (VBA) custom functions, at least in regard to sharing and updating the formulas.
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)
## Lifetime (30-year) ASCVD Risk Calculator
Here are the references:
[Goff DC Jr, Lloyd-Jones DM, Bennett G, et al. 2013 ACC/AHA Guideline on the Assessment of Cardiovascular Risk: A Report of the American College of Cardiology/American Heart Association Task Force on Practice Guidelines. J Am Coll Cardiol. 2013 Nov 12.](https://pubmed.ncbi.nlm.nih.gov/24239921/)
[Lloyd-Jones DM, Leip EP, Larson MG, et al. Prediction of lifetime risk for cardiovascular disease by risk factor burden at 50 years of age. Circulation. 2006 Feb 14;113(6):791-8. Epub 2006 Feb 6.](https://pubmed.ncbi.nlm.nih.gov/16461820/)
You can find this in lots of places. There's an official one at [American College of Cardiology](https://tools.acc.org/ascvd-risk-estimator-plus/#!/calculate/estimate/). There's also a bunch of derivatives I made at [cliniborg.org](https://www.cliniborg.org/tag/ascvd/). But at work you may be using an excel file for this type of stuff.
So today I updated [the Frankenstein spreadsheet](/frankenstein) with the custom function listed below.
```
Function LIFETIMEASCVDRISK(gender, age, tobacco, diabetes, total_cholesterol, systolic_blood_pressure)
If LCase(gender) = "male" Or LCase(gender) = "female" Then
GoTo Line1
Else
MsgBox "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
MsgBox "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
MsgBox "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
MsgBox "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
MsgBox "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
MsgBox "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
```
Thanks for reading!