A not so Simple Moving Average

[ivorykeys87]

I have a slightly complex problem I'm trying to figure out in excel. I need a simple moving average, lets say for a period of 3, however, I only want it to average negative or positive numbers in the range. So, in other words, say we're only averaging negatives in this range (-5,-4,-6) we would get a return of -5. But lets say the next number is a positive (-5,-4,-6,3), I still need it to return -5 by completely over looking the positive integers and only looking at the last 3 negative ones. And again, if the range continued to this (-5,-4,-6,3,-8), we should get -6. Is this possible??

[queuesef]

NOTE: Formula needs Ctrl + Shift + Enter when completed! I have 20 numbers in A1 to A20 with negative numbers mixed in. In Cell A25 I have the formula: =AVERAGE(IF(A1:A20<0,A1:A20,"")) for the NEGATIVES ( the Less Than 0 portion) and in cell A26 I have =AVERAGE(IF(A1:A20>0,A1:A20,"")). Make sure the CURLY Brackets surround the formula, which you get if you were in a cell Editing one of the above formulas (in A25 or A26) and do the Ctrl + Shift + Enter when completed. I got averages for the LESS Than 0 (Negatives) in cell A25; And Greater Than 0 (Positives) in A26.
GiVe Me SoMe CrEdiT if this WoRkS for YoU! THx

[ivorykeys87]

It works, but not as intended. I need a constant average of the 3 most recent negative or positive numbers in a range. Notice, in my last example (-5,-4,-6,3,-8) the 3 is excluded from the calculation because it is positive and the -5 is excluded from the calculation because it is more than 3 negative integers back in the series of numbers. It's only calculating the average based on the -8,-6,and -4. But I appreciate the effort, and any other help.

[JohnTopley]

Try

Data in column B1 down

"Helper" in D

in D1 and copy down

=COUNTIF($B$1:B1,"<0")

To get average

=AVERAGE(IF((D1:D100>=MAX(D1:D100)-2)*(B1:B100<0),B1:B100))


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[ivorykeys87]

That was a huge help! It still needed some doctoring but here is what was required.

For the negative aspect: =AVERAGE(IF(($D$1:D9>=MAX(D2:D9)-2)*($B$1:B9<0),$B$1:B9))
And the positive aspect: =AVERAGE(IF(($E$1:E3>=MAX(E2:E3)-2)*($B$1:B3>0),$B$1:B3))

Now, it's just a matter of applying it to inter day trading data (Open, High, Low, Close) per a five minute chart. And then finally applying it to an actual trading platform to graph it out in real time. By all means, if anyone is interested in helping with this little project, contact me personally. If it works well, it'll be the only technical indicator of its kind.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.