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

On Thu, 05 Jun 2008 20:18:17 +0800, Andrew Bourke <(E-Mail Removed)> 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("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

Thank you.

Ron Rosenfeld wrote:
> On Thu, 05 Jun 2008 20:18:17 +0800, Andrew Bourke <(E-Mail Removed)> 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("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

On Fri, 06 Jun 2008 07:16:23 +0800, Andrew Bourke <(E-Mail Removed)> wrote:

>Thank you.

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