Calculation on a range problem
 

Hi
Need a little help please.


Cells C3 to C7 contain these values.
The e indicates an estimated score and so needs to remain with the number.

45
73
50e
29
88

After some help from Bob Phillips, I got Avv2 to work.

Sub Avv2()
Dim rng As Range
Range("D3:D7") = Application.Substitute(Range("C3:C7"), "e", "")
Set rng = Range("D3:D7")
[C9] = Application.Average(Range("D3:D7"))
[D9] = Application.Average(rng)
End Sub


But I can't get any of the following lines of code to work in Avv1.
The first two lines with rng cause compiler errors whilst the last line
[C9] =
doesn't give a compiler error but returns #DIV/0! in [C9].


Sub Avv1()
Dim rng As Range
Set rng = Range(Application.Substitute(Range("D3:D7"), "e", ""))

rng = Range(Application.Substitute(Range("D3:D7"), "e", ""))

[C9] = Application.Average(Application.Substitute(Range(" C3:C7"), "e",""))
End Sub

I'm just trying to find the average for the list of numbers and I would
like to do this without having to copy them stripped down somewhere else
first.

TIA

Andrew

Calculation on a range problem
 

On Thu, 05 Jun 2008 20:18:17 +0800, Andrew Bourke wrote:

Hi
Need a little help please.


Cells C3 to C7 contain these values.
The e indicates an estimated score and so needs to remain with the number.

45
73
50e
29
88

After some help from Bob Phillips, I got Avv2 to work.

Sub Avv2()
Dim rng As Range
Range("D3:D7") = Application.Substitute(Range("C3:C7"), "e", "")
Set rng = Range("D3:D7")
[C9] = Application.Average(Range("D3:D7"))
[D9] = Application.Average(rng)
End Sub


But I can't get any of the following lines of code to work in Avv1.
The first two lines with rng cause compiler errors whilst the last line
[C9] =
doesn't give a compiler error but returns #DIV/0! in [C9].


Sub Avv1()
Dim rng As Range
Set rng = Range(Application.Substitute(Range("D3:D7"), "e", ""))

rng = Range(Application.Substitute(Range("D3:D7"), "e", ""))

[C9] = Application.Average(Application.Substitute(Range(" C3:C7"), "e",""))
End Sub

I'm just trying to find the average for the list of numbers and I would
like to do this without having to copy them stripped down somewhere else
first.

TIA

Andrew



You could use an array worksheet formula:

=AVERAGE(--LEFT(rng,LEN(rng)-IF(NOT(ISNUMBER(rng)),1)))

To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula.

If this must be done in VBA, then:

============================
Option Explicit
Function AvgRng(rng As Range) As Double
Dim c As Range
Dim dSum As Double
For Each c In rng
dSum = dSum + Val(c.Value)
Next c

AvgRng = dSum / rng.Count

End Function
==============================
--ron

Calculation on a range problem
 

Thank you.

Ron Rosenfeld wrote:
On Thu, 05 Jun 2008 20:18:17 +0800, Andrew Bourke wrote:

Hi
Need a little help please.


Cells C3 to C7 contain these values.
The e indicates an estimated score and so needs to remain with the number.

45
73
50e
29
88

After some help from Bob Phillips, I got Avv2 to work.

Sub Avv2()
Dim rng As Range
Range("D3:D7") = Application.Substitute(Range("C3:C7"), "e", "")
Set rng = Range("D3:D7")
[C9] = Application.Average(Range("D3:D7"))
[D9] = Application.Average(rng)
End Sub


But I can't get any of the following lines of code to work in Avv1.
The first two lines with rng cause compiler errors whilst the last line
[C9] =
doesn't give a compiler error but returns #DIV/0! in [C9].


Sub Avv1()
Dim rng As Range
Set rng = Range(Application.Substitute(Range("D3:D7"), "e", ""))

rng = Range(Application.Substitute(Range("D3:D7"), "e", ""))

[C9] = Application.Average(Application.Substitute(Range(" C3:C7"), "e",""))
End Sub

I'm just trying to find the average for the list of numbers and I would
like to do this without having to copy them stripped down somewhere else
first.

TIA

Andrew



You could use an array worksheet formula:

=AVERAGE(--LEFT(rng,LEN(rng)-IF(NOT(ISNUMBER(rng)),1)))

To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula.

If this must be done in VBA, then:

============================
Option Explicit
Function AvgRng(rng As Range) As Double
Dim c As Range
Dim dSum As Double
For Each c In rng
dSum = dSum + Val(c.Value)
Next c

AvgRng = dSum / rng.Count

End Function
==============================
--ron

Calculation on a range problem
 

On Fri, 06 Jun 2008 07:16:23 +0800, Andrew Bourke wrote:

Thank you.

You're welcome. Glad to help.
--ron