Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: conditional averaging - this one could be tricky

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay....

    outstanding mate one quick question though.

    Why did u use this line..

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

    why the +100??

    Ed

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay that is great,

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

    Ed

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •