Exclude cell from average calculation

[Dave Ramage]

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

 

[Jim Thomlinson]

Why not just change the value from a number to text. The value will left
align itself as text so you will know that it is excluded and the formula
will just exclude the value.

 

[Don Guillett]

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))

 

[bhofsetz]

Don,
How would you insert an array formula like this one using VBA
code?

 

[Dave Ramage]

Thanks Jim, this works very nicely.

If anyone has the same requirement in the future, here is my final code:
Const cExcludedTextColour As Long = 12632256 'colour for text of excluded
cells , default is 12632256 (light grey)

Private Sub ExcludeResult()
'''Add ' 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 Is IncludeExcludeTarget Then 'don't run in other
workbooks/worksheets. IncludeExcludeTarget is the object name of the
worksheet this need to work on.
Set rngS = Selection
For Each rngA In rngS.Areas
For Each rngR In rngA.Cells
strTemp = rngR.Formula
If rngR.PrefixCharacter <> "'" And Left(strTemp, 1) <> "="
And Len(strTemp) > 0 And IsNumeric(strTemp) Then
'not already excluded, not a formula, not blank, is
numeric
rngR.Formula = "'" & strTemp
rngR.Font.Color = cExcludedTextColour
rngR.HorizontalAlignment = xlRight 'otherwise text would
align left
End If
Next rngR
Next rngA
End If
End Sub

Private Sub IncludeResult()
'''Remove ' prefix from 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 Is IncludeExcludeTarget Then
Set rngS = Selection
For Each rngA In rngS.Areas
For Each rngR In rngA.Cells
If rngR.PrefixCharacter = "'" And IsNumeric(rngR.Formula) Then
rngR.Formula = rngR.Value 'removes ' prefix from value
rngR.Style = "Normal" 'removes text colour and forced
right alignment
End If
Next rngR
Next rngA
End If
End Sub

Cheers,
Dave

 

[Don Guillett]

formulaarray

 

[bhofsetz]

Thanks Don.

Is there a way to keep the array formula from converting back to a
regular formula if the user clicks in the cell containing the array
formula?

From my limited experience with array formulas I've noticed that each
time you click in the cell it changes the formula from an array formula
to a regular formula unless you exit the cell by hitting
ctrl+shift+enter.

Basically I'd like my user to be able to click in the cell so that it
will show what range is being affected by the formula, then go on to
another cell or whatever they want to do, without having to hit
ctrl+shift+enter and without the formula changing from an array
formula.

Maybe I'm asking for too much but is this possible?

p.s. I tried protecting the sheet to keep the array formulas from
changing but then the ability to click in the cell and have the
affected range highlighted isn't avaliable.