conditional averaging - this one could be tricky

[Jackeb]

Q1. Is there a way to average a range(dnamic) of 6 or 7 numbers with conditions?

Q2 Failing number 1.

I have a list of calculated figures, this list has a large spread and a large scatter. From this i need to filter and trend the points.

The filter should look at a number (call it the primary) and take into account the previous six days (or so) numbers (call these the rolling average) and judge wether the primary number should be there.

simple so far, well relativly, the tricky bit is, once you have assessed the primary and decided that it is not valid, how do you omit it from the rolling average?

The code I have used is in three colums (M,N,O), on with a list of numbers:

Delta Performance
-3.7
-6.5
-10.1
-7.2
-28.6
-4.2
-13.8
-13.1
-15.4
-1.0
-3.9
-8.1
-21.4
-17.2
-18.8
-18.9
-17.2
-10.3
-14.8
-15.6
-21.6
-16.0
-19.6
-13.3
15.7
-18.1
-8.4
-10.8

The next one has this formula, starting from row 8:

=IF(ISERROR(M13),"",IF(IF(AND(M13<(1-Variables!$H$9/100)*O12,M13>(1+Variables!$H$9/100)*O12),M13,FALSE)=FALSE,"*",M13))

where variables!H9 is say 30

and a third column with this code:

=AVERAGE(IF(N13="*",O12,N13),IF(N12="*",O11,N12),IF(N11="*",O10,N11),IF(N10="*",O9,N10),IF(N9="*",O8,N9),IF(N8="*",O7,N8),IF(N7="*",O6,N7),IF(N6="*",O5,N6),IF(N5="*",P4,N5))

starting also in row 8.

as I am sure you can see this is a very cumbersom and difficult code to use. I am trying to make it portable and wish to write it as a function but now I am getting a little over my head, so I thought i would tap the brains on this board to see if you could come up with a simplified version that could be made into a (quick) function as it is not only cumbersome but very, very slow (20000 rows or so)

Hope you can help and any more info just ask!!

Ed

[ This Message was edited by: Jackeb on 2002-04-29 05:48 ]

[ This Message was edited by: Jackeb on 2002-04-29 05:48 ]

[Jay Petrulis]

Hi,

I don't fully understand your problem, but here are some short replies.

Q1: Yes, what are the conditions?

Q2: What determines whether the primary belongs or not?

Bye,
Jay

[Jackeb]

Jay,

sorry it is a difficult one to state, I think that the easiest way to state the problem is to say

I want a colum 'B' averaging the previous six numbers 'A'

ie =average(A1:A6)

the problem is that i need to include a filter in this process, so i suppose it is a two stage process really the averageing and the filtering

For the filter I need to be able to determine wether or not the value for day 2 is < + or > - 30% of the value of day 1, if it is then remove it from calculation if not then include it in the calculation, there are a few refinments that I would like but they are bits around the edge, this is the core of the problem, the example above does it (just) in a worksheet but I need to put it into a function.

VCheers

Ed

[Jay Petrulis]

Hi Ed,

Please test this UDF against your data. It will always include the first cell in the range, and thereafter only include the cells where the change from the previous day is under the threshold value (0.3 in my tests).

It then takes the average of the resulting set.

'---begin VBA---
Function AverageExt(Rng As Range, Threshold As Double) As Double
Dim x, y, z, a, b, c, sumseries

x = Rng.Value
a = Rng.Count
ReDim z(1 To a, 1 To 2)

For y = 1 To a
z(y, 1) = 100 + x(y, 1)

If y = 1 Then
z(y, 2) = z(y, 1)
Else
z(y, 2) = WorksheetFunction.Round(Abs(z(y, 1) - z(y - 1, 1)) / z(y - 1, 1), 4)
End If
Next y

c = a
sumseries = x(1, 1)
For b = 2 To a
If z(b, 2) > Threshold Then
c = c - 1
Else
sumseries = sumseries + x(b, 1)
End If
Next b

AverageExt = sumseries / c
End Function
'---end VBA---

Call as
=AVERAGEEXT(Range,Critical threshold)

Bye,
Jay

[Jackeb]

Jay, that is great mate.

Looking back i forgot to mention a vital piece of information, the average is going to be used to plot a trend so it must display a colum of numbers, but i have made a few small mods and bar the testing, works great!!

Thanx again

Ed

[Jackeb]

Jay....

outstanding mate one quick question though.

Why did u use this line..

z(y, 1) = 100 + x(y, 1)

why the +100??

Ed

[Jay Petrulis]

On 2002-05-01 09:03, Jackeb wrote:
Jay....

outstanding mate one quick question though.

Why did u use this line..

z(y, 1) = 100 + x(y, 1)

why the +100??

Ed

How am I supposed to know? That was yesterday!!

Actually, as I understood your question, the raw data was percentage change from the previous day. Thus, a value of -15 really meant 1-.15 = .85, or since these were the actual numbers 100%-15% = 85%

This was needed to get the relative change, or else, -1 on day 1 to -1.5 on day 2 would be calculated as a 50% change and would be erroneously excluded.

If I've misunderstood your request, please let me know.

Bye,
Jay

EDIT: By the way, you do not need the rounding in the function.

z(y, 2) = WorksheetFunction.Round(Abs(z(y, 1) - z(y - 1, 1)) / z(y - 1, 1), 4)

could be

z(y, 2) = Abs(z(y, 1) - z(y - 1, 1)) / z(y - 1, 1)

without any real loss for you.


[ This Message was edited by: Jay Petrulis on 2002-05-01 09:14 ]

[Jackeb]

Jay that is great,

what do you do mate? You seem to be particually swift around the numbers?

Ed