conditional averaging - this one could be tricky

Jackeb

Board Regular
Joined
Mar 20, 2002
Messages
81
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Jay....

outstanding mate one quick question though.

Why did u use this line..

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

why the +100??

Ed
 
Upvote 0
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!! :biggrin:

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
 
Upvote 0
Jay that is great,

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

Ed
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top