Average of Named Range

[jjlafond]

Hello,

I have a long string of numbers, some negative some positive (representing a Sine curve).

I am trying to average consecutive set of positive and negative numbers (average the 10 positive numbers, then 9 negative numbers, then the following 11 positive numbers, etc)

I currently have:

'All values have been reduced to 1, 0, -1 using Sgn()
Set SinRange = Range("E3")For r = 3 To Range("A" & Rows.Count).End(xlUp).Row
    g = r - 1
    h = r + 1
    If Cells(r, "F").Value = Cells(g, "F").Value Then
        SinRange = Union(SinRange, Cells(r, "E"))
    ElseIf Cells(r, "F").Value = "0" Then
        SinRange = Union(SinRange, Cells(r, "E"))
    Else:
        Cells(r, "G").Value = WorksheetFunction.Average(SinRange.Value)
        Set SinRange = Cells(h, "E")
    End If
Next

I've tried several different variations to no effect. I believe my issue is in the average.

I am currently trying to add a cell to the named range SinRange if it has the same sign, then taking an average of all of those grouped cells. When it "averages", it only has one number and it takes the "average" of that one number.

With this current method, I need to have all values in SinRange to be individually represented so I can take an average of the 10 or so positive numbers.

Any ideas or suggestions?

 

[RickXL]

Hi,

I have not checked the full logic but I can see where something is missing.

This:

SinRange = Union(SinRange, Cells(r, "E"))

should be;

Set SinRange = Union(SinRange, Cells(r, "E"))