If you are using Excel 97, consult "Excel 97 Display Bug" for information about a bug in Excel 97. One way to work around this bug is to modify the code for these Visual Basic functions, as discussed on that web page.

1. Press here to download the Excel spreadsheet VBASIC.XLS (17,408 bytes) that contains a Visual Basic module with the code from Figures 4.5, 4.12, and 7.5 of Strategic Decision Making. Depending on how your web browser is configured, it may automatically start Excel and load VBASIC.XLS into Excel. If this happens, save the file under the name VBASIC.XLS. If the browser does not automatically start Excel, then it will ask you what name you want to save the file under. Tell it VBASIC.XLS. After the file is downloaded, you can start Excel to access it. (This file is in Excel 5.0 format, and it can be loaded into this version or any newer version of Excel.)
2. Copy the Visual Basic code below into the Clipboard, and then paste it into an Excel macro module. (Note: Copy this code directly from the page below. Do not copy it from the html source page because that contains special coding necessary for the ``greater than'' sign in html.)

Function ValuePL(x, Xi, Vi)
  i = 2
  Do While x >  Xi(i)
    i = i + 1
  Loop
  ValuePL = Vi(i - 1) _
      + (Vi(i) - Vi(i - 1)) * (x - Xi(i - 1)) / (Xi(i) - Xi(i - 1))
End Function

Function ValueE(x, Low, High, Monotonicity, Rho)
  Select Case UCase(Monotonicity)
    Case "INCREASING"
      Difference = x - Low
    Case "DECREASING"
      Difference = High - x
  End Select
  If UCase(Rho) = "INFINITY" Then
    ValueE = Difference / (High - Low)
  Else
    ValueE = (1 - Exp(-Difference / Rho)) / (1 - Exp(-(High - Low) / Rho))
  End If
End Function

Function CEValue(RhoM, Weight, Pi, Vi)
  If UCase(RhoM) = "INFINITY" Then
    CEValue = Weight * Application.SumProduct(Pi, Vi)
  Else
    EV = 0
    For i = 1 To Application.Count(Pi)
      EV = EV + Pi(i) * Exp(-Weight * Vi(i) / RhoM)
    Next i
    CEValue = -RhoM * Log(EV)
  End If
End Function

Function Quad(x, Xi, Yi)
  Xnorm = (x - Xi(1)) / (Xi(3) - Xi(1))
  Xm = (Xi(2) - Xi(1)) / (Xi(3) - Xi(1))
  Ym = (Yi(2) - Yi(1)) / (Yi(3) - Yi(1))
  a = (Ym - Xm) / (Xm * Xm - Xm)
  b = 1 - a
  Ynorm = a * Xnorm * Xnorm + b * Xnorm
  Quad = Yi(1) + Ynorm * (Yi(3) - Yi(1))
End Function

Last updated August 25, 2008.