Calculation on a range problem

[Andrew Bourke]

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("D37") = Application.Substitute(Range("C3:C7"), "e", "")
Set rng = Range("D37")
[C9] = Application.Average(Range("D37"))
[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("D37"), "e", ""))

rng = Range(Application.Substitute(Range("D37"), "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

 

[Ron Rosenfeld]

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("D37") = Application.Substitute(Range("C3:C7"), "e", "")
Set rng = Range("D37")
[C9] = Application.Average(Range("D37"))
[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("D37"), "e", ""))

rng = Range(Application.Substitute(Range("D37"), "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

 

[Andrew Bourke]

Thank you.

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("D37") = Application.Substitute(Range("C3:C7"), "e", "")
Set rng = Range("D37")
[C9] = Application.Average(Range("D37"))
[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("D37"), "e", ""))

rng = Range(Application.Substitute(Range("D37"), "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

 

[Ron Rosenfeld]

Thank you.

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