categories: spreadsheet   endocrine   gastroenterology

I’m working on using custom excel functions rather than formulas in the cell.

The Frankenstein spreadsheet 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.

Below I’ve got some of the custom functions that will be finding their way to the Frankenstein spreadsheet. 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

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

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!