Hello.

Could I please be helped with two evaluations?

1. Output the average times-in-a-row price has been at or above BB 18 1.78 over the last 175 bars. What I'm looking for here is "what is average consecutive run length of close being at or above that BB line".

2.  Over the last 175 bars, how many bars was the max consecutive run of price at or above the same BB line?

Thank you very much.

For the average, maybe something like the following?

SUM(IIF(TrueInRow(C < BBTOP(1.78, 18), 2) = 1, TrueInRow(C1 >= BBTOP(1.78, 18, 1), 10), 0), 175) / CountTrue(TrueInRow(C < BBTOP(1.78, 18), 2) = 1, 175)

For the maximum, maybe something like the following?

MAX(IIF(TrueInRow(C < BBTOP(1.78, 18), 2) = 1, TrueInRow(C1 >= BBTOP(1.78, 18, 1), 10), 0), 175)

Note that the run needs to have finished in order to get counted and included in the average. A current run where price is still at or above the top Bollinger Band is not included in the average or maximum.

Thank you, Bruce. These work well. I appreciate the help.

Bruce, a question.
What's the 10 here? 1.78, 18, 1), 10

MAX(IIF(TrueInRow(C < BBTOP(1.78, 18), 2) = 1, TrueInRow(C1 >= BBTOP(1.78, 18, 1), 10), 0), 175)

Thank you.

The TrueInRow() function is not open ended. The 10 is the maximum number of bars in a row for which the formula checks. The number returned by:

TrueInRow(C1 >= BBTOP(1.78, 18, 1), 10)

Ranges from 0 through 10. If it is true 20 bars in a row, it would still return 10 because it would not be checking bars 11 through 20.

Gotcha. So 10 is a limit, if I understand correctly?

Yes, it is a limit of sorts. It limits the maximum number of bars in a row. You can however change the 10 to a different number of you want. Note that setting this too high can make the formula slower.

I see, so not a system limit (like, say, an absolutely max lookback) It just defines the end limit to include for the formula itself.  If I change that, does anything else need to change? 

One related question: Have I correctly used EMA8 in the same formula below?

Nothing else would need to be changed.

Your formula for EMA8 is close, but the second part does not correctly convert XAVGC8 to XAVGC8.1 to get the one bar ago version.

MAX(IIF(TrueInRow(C < BBTOP(1.78, 18), 2) = 1, TrueInRow(C1 >= XAVGC8.1, 10), 0), 175)

Ah, oops. I actually knew that. I'm not sure if it matters that I knew that, since knowing that didn't change what I did. 

Thanks again, Bruce. Have a nice weekend.

You're welcome. Have a nice weekend.