starting with custom excel spreadsheet functions
categories:
Spreadsheet
Using custom excel functions rather than formulas in the cell.
The [Frankenstein spreadsheet](/frankenstein) started out with cell-based calculations, but it may be better to use Visual Basic for Applications (VBA) custom functions, at least in regard to sharing and updating the formulas.
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).
Below I've got some of the custom functions that will be finding their way to the [Frankenstein spreadsheet](/frankenstein). To view this on your own spreadsheed, you'd 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)
Normal 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)
## Body Mass Index
For the metric system this is height in centimeters divided by weight in kilograms squared. However, this formula is for the imperial calculation (inches and pounds).
```
Function BMIIMPERIAL(heightinches, weightpounds)
BMIIMPERIAL = 703 * weightpounds / heightinches ^ 2
BMIIMPERIAL = Application.Round(BMIIMPERIAL, 2)
End Function
```
## Corrected Calcium
Sometimes you're trying to determine if the patient is actually hypercalcemic and you don't have an ionized calcium.
```
Function CALCIUMCORRECTED(calcium, albumin)
If IsEmpty(calcium) Then
MsgBox "calcium field is empty"
GoTo Linezilch
ElseIf calcium < 0 Then
MsgBox "calcium cannot be 0 or less"
GoTo Linezilch
End If
If IsEmpty(albumin) Then
MsgBox "albumin field is empty"
GoTo Linezilch
ElseIf albumin < 0 Then
MsgBox "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
```
## APRI (AST to Platelet Ratio Index)
With this one, you're trying to get an idea if the patient has cirrhosis. This one has two parts, the calculated index and the interpretation.
The calculated index is below:
```
Function LIVERAPRI(ast, platelets)
If IsEmpty(ast) Then
MsgBox "ast field is empty"
GoTo Linezilch
ElseIf ast < 0 Then
MsgBox "ast cannot be 0 or less"
GoTo Linezilch
End If
If IsEmpty(platelets) Then
MsgBox "platelets field is empty"
GoTo Linezilch
ElseIf platelets < 0 Then
MsgBox "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 interpretation part is below:
```
Function LIVERAPRIINTERPRETATION(apri)
If IsEmpty(apri) Then
MsgBox "apri field is empty"
GoTo Linezilch
ElseIf apri < 0 Then
MsgBox "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
```
## ANI (The ALD/NAFLD Index)
So it is actually an acronym of acronyms. ANI stands for "ALD/NAFLD Index" where ALD is "alcoholic liver disease" and NAFLD is "non-alcoholic fatty liver disease". This one also has two parts, the index and then the probability of alcoholic liver disease.
The index calculation is below:
```
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 probability calculation is below:
```
Function LIVERANIPROBABILITY(ani)
LIVERANIPROBABILITY = (Exp(ani)) / (1 + (Exp(ani)))
LIVERANIPROBABILITY = Application.Round(LIVERANIPROBABILITY, 2)
End Function
```
More to come!