Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Problem | Excel Worksheet Functions | |||
calculation problem... | Excel Programming | |||
Calculation problem - please help | Excel Discussion (Misc queries) | |||
Calculation problem | Excel Programming | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |