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
|