CODEWITHSUNDEEP
excelvbaformulaaverage
Aman Singh Dhir
Aman Singh Dhir

Reputation: 23

average of only numerical part of cells, avoid any letters

I am trying to work a formula to tell me the average of a cell that contain numbers but also letters. 

Price
$486.5/mt,
$287/mt,
$286.75/mt,
$286.5/mt,
$286.75/mt,

So each cell contains one of the above for example cell F2 contains '$486.5/mt,'. My question is what formula can I use to only return number average. For now, the average formula doesn't work even work. I tried using "LEFT" formula, but that won't work because the $ sign comes before the value.

Please help!

Thanks

Upvotes: 0

Views: 81

Answers (3)

iDevlop
iDevlop

Reputation: 25272

You can do this without VBA, using an array formula like this one: 

=AVERAGE(VALUE(MID(K18:K22,2,LEN(K18:K22)-5)))

(values in K18:K22)

Upvotes: 2

Brandon Barney
Brandon Barney

Reputation: 2392

Taking a similar approach to braX, this function will explicitly convert strings that are in the predefined format ($.../...) which will prevent any issues from an improperly formatted (or otherwise invalid) string. This allows greater control over the return.

Public Function GetAmountFromString(ByVal InputString As String) As Variant
    If InputString Like "$*/*" Then
        Dim ProcessedString As String
        ProcessedString = Replace(InputString, "$", vbNullString)

        Dim SplitString As Variant
        SplitString = Split(ProcessedString, "/")

        ' Explicitly convert String to Currency.
        GetAmountFromString = CCur(SplitString(0))
    Else
        GetAmountFromString = vbNullString
    End If
End Function

This function will first check for the format. As long as the input is in the correct format it will then replace the '$' symbol with a null string. Finally, it will split the string into two parts using the '/'. It will return a Currency version of the value before the '/' symbol.

In the event of an improper input it will return a null string. This can be customized as you wish (you can replace vbNullString with "ThisIsAnInvalidInput" and it will still work just fine.

To use this function, add it to your VBProject, and then use the function =GetAmountFromString(Cell) in your worksheet.

You can also use this function from other subroutines (if, for example, you wanted to use the function within a subroutine that averaged valid values only).

Upvotes: 0

braX
braX

Reputation: 11755

You can use this function:

Public Function NumbersOnly(str As String) As Single
  Dim c As String
  Dim nIndex As Integer
  Dim sResult As String

  For nIndex = 1 To Len(str)
    If IsNumeric(Mid$(str, nIndex, 1)) Or Mid$(str, nIndex, 1) = "." Then
      sResult = sResult & Mid$(str, nIndex, 1)
    End If
  Next
  NumbersOnly = Val(sResult)
End Function

and then the formula for cell F3 would be:

=NumbersOnly(F2)

Upvotes: 0

Related Questions