you could have a helper column with x for cells to exclude and use this
ARRAY formula which must be entered/edited with ctrl+shift+enter instead of
just enter.
=AVERAGE(IF(F1:F3<"x",E1:E3))


--
Don Guillett
SalesAid Software

"Dave Ramage" <Dave wrote in message
...
I have a worksheet that contains test results in various rows/columns. A
formula at the bottom of the column of data calculates stats on this data
e.g:
=average(B2,B5,B8,B11,B13)

Occasionally an individual value is identified as an outlier and must be
excluded from the average calculation. To make this easy I'm thinking
about
adding two items to the Cell shortcut menu (the one you get when you
right-click over a cell)- "Exclude cell", and "Include cell".

Currently, to exclude a cell from the average calculation I insert an "x"
before the value. This works, but does not look so good. Can anyone think
of
a better way to exclude an individual cell from the calculation without
changing the formula itself? Is there a character other than "x" that
would
not be seen, or any other method?

Very grateful for any help...

Cheers,
Dave

Here's the code I'm using behind the shortcut menu:
Const cAppTitle As String = "AppTitle"
Const cExcludeSymbol As String = "x" 'string to add or remove indicating
excluded cell

Private Sub ExcludeResult()
'''Add a "x" to the start of a cell to indicate exclusion from
calculations
' Run from worksheet shortcut menu

Dim rngS As Range, rngA As Range, rngR As Range
Dim strTemp As String

If ActiveSheet.Parent Is ThisWorkbook Then 'don't run in other workbooks
If TypeName(Selection) = "Range" Then 'don't run if chart etc is
selected
Set rngS = Selection
For Each rngA In rngS.Areas
For Each rngR In rngA.Cells
strTemp = rngR.Formula
If Left(strTemp, Len(cExcludeSymbol)) < cExcludeSymbol
And
Left(strTemp, 1) < "=" And Len(strTemp) 0 And IsNumeric(strTemp) Then
'not already excluded, not a formula, not blank, is
numeric
rngR.Formula = cExcludeSymbol & strTemp
rngR.Font.ColorIndex = 15
End If
Next rngR
Next rngA
Else
MsgBox prompt:="Please select a cell to include or exclude
first!",
Buttons:=vbExclamation, Title:=cAppTitle
End If
End If
End Sub

Private Sub IncludeResult()
'''Remove a "x" from the start of a cell to remove exclusion from
calculations
' Run from worksheet shortcut menu

Dim rngS As Range, rngA As Range, rngR As Range
Dim strTemp As String

If ActiveSheet.Parent Is ThisWorkbook Then
If TypeName(Selection) = "Range" Then
Set rngS = Selection
For Each rngA In rngS.Areas
For Each rngR In rngA.Cells
strTemp = rngR.Formula
If Left(strTemp, Len(cExcludeSymbol)) = cExcludeSymbol
Then
rngR.Formula = Right(strTemp, Len(strTemp) -
Len(cExcludeSymbol))
rngR.Style = "Normal"
End If
Next rngR
Next rngA
Else
MsgBox prompt:="Please select a cell to include or exclude
first!",
Buttons:=vbExclamation, Title:=cAppTitle
End If
End If
End Sub