VLookup on a DataValidation multiselect column

[heathn101]

I am looking for some help with the following, I have two columns (BA and BD) where I set up (VBA code at the bottom, used with permission) a multi selection drop-down for each cell. The values available are (-,--,Ø,+,++) so that cell BA2 shows several of the symbols in a comma separated list for example: Ø,+,-,++,+. The lookup table is on the second sheet (Lists) and has a numerical value for each symbol in a two column table, Symbol:Value. I want to do a formaula, or VBA code, that completes a lookup that finds the numerical value for each symbol, then averages the values, so in the example list, you would get (3,4,2,5,4) SUM = 18, AVG = 3.6. I would like the AVG (3.6 in this case) to display in the cell next to the cell containing the symbol list (BB2 for example).





VBA code

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If (Target.Column = 53 Or Target.Column = 56) Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub


Thanks for any and all help