|
Posted to microsoft.public.excel.misc
|
|
|
formulaForAdding
huh? VB again? hahaha
"Ron Rosenfeld" wrote:
On Mon, 17 Sep 2007 17:02:03 -0700, kyoshirou
wrote:
Hi,
If i have some numbers inside a column, means i enter value 5.8, then
Alt+Enter, i enter another value 6.0, then Alt+Enter, follow by value of 8.0,
etc
Can i calculate the Average or total of the sum in that single column anot?
coz if single value inside a single column, i can drag n calculate auto.
Any sugguestions?
Thanks!
It seems your values will all be in a single cell, each on a separate line.
If that is the case, then you first have to parse out the values; and then
apply the appropriate function.
To parse out the values, a UDF is handy. To enter the UDF, <alt-F11 opens the
VB Editor. Ensure your project is highlighted in the project explorer window,
then Insert/Module and paste the code below into the window that opens.
To use this, enter a formula of the type:
=SUM(Nums(cell_ref))
===========================================
Option Explicit
Function Nums(str As String) As Variant
Dim Re As Object
Dim MC As Object
Dim t() As Double
Dim i As Long
Set Re = CreateObject("vbscript.regexp")
Re.MultiLine = True
Re.Global = True
Re.Pattern = "^[\-+]?\d*\.?\d+$"
If Re.test(str) = False Then
Nums = 0
Else
Set MC = Re.Execute(str)
ReDim t(MC.Count - 1)
For i = 0 To UBound(t)
t(i) = MC(i)
Next i
End If
Nums = t
End Function
===============================================
--ron
|