formulaForAdding

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Those values aren't really numeric. They're text entries that just use
numerals.

If you have any choice at all in the layout of the data, put each number in a
different cell.
 
If you have a series of values in a single cell separated by ALT-ENTER, then
1. select the cell
2. Data > Text to Columns > Delimited > Next > Other >
click in the box next to the Other checkbox
While holding down the ALT key, touch 010
Finish

This will split up the data into individual cells, then addition will be easy.
 
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!
Click to expand...

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
 
huh? VB again? hahaha


Ron Rosenfeld said:
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!
Click to expand...

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
Click to expand...
 
Yes. I appreciate that. And ur fast respond.

Ron Rosenfeld said:
That's a useful and thoughtful response to an attempt to provide you some
assistance.

I'll try not to make that mistake again.
--ron
Click to expand...
 
What do you mean? The instructions which Gary''s Student gave you will put
your 3 numbers in 3 consecutive columns.
 
Back
Top