Bruce,

Previously you showed me a formula for Green Days in a Row (with higher highs). Which is awesome!

TrueInRow(C > O and C > H1, 10)

Now I want to add a column to the results to sort by the stock that had the biggest overall range during those green days, ie, the percent change from the Low to the High of that number of bars. I need the Low for the first day of the green run. The syntax for L is:

L(z) -> Low Price of specified bar.

If I use the TrueInRow formula for Z, it gives me an error. z has to be a number, not a formula. So I'm stumped.

Regards,
Derek

I really wish you could put a formula in there, but it is not yet possible. You can nest a bunch of IIF() functions inside of each other however. Maybe something like the following?

IIF(C <= O OR C <= H1, 0, IIF(C1 <= O1 OR C1 <= H2, H - L, IIF(C2 <= O2 OR C2 <= H3, MAXH2 - MINL2, IIF(C3 <= O3 OR C3 <= H4, MAXH3 - MINL3, IIF(C4 <= O4 OR C4 <= H5, MAXH4 - MINL4, IIF(C5 <= O5 OR C5 <= H6, MAXH5 - MINL5, IIF(C6 <= O6 OR C6 <= H7, MAXH6 - MINL6, IIF(C7 <= O7 OR C7 <= H8, MAXH7 - MINL7, IIF(C8 <= O8 OR C8 <= H9, MAXH8 - MINL8, IIF(C9 <= O9 OR C9 <= H10, MAXH9 - MINL9, MAXH10 - MINL10))))))))))

Bruce,

If brute force is what it takes, I'm ok with that! That was really good. Thank you!

I changed it a little to calculate the percent change from the low of the first green day. That's what I wanted to find, the biggest percent change.

IIF(C <= O OR C <= H1, 0, IIF(C1 <= O1 OR C1 <= H2, 100*(H - L)/L, IIF(C2 <= O2 OR C2 <= H3, 100*(MAXH2 - MINL2)/MINL2, IIF(C3 <= O3 OR C3 <= H4, 100*(MAXH3 - MINL3)/MINL3, IIF(C4 <= O4 OR C4 <= H5, 100*(MAXH4 - MINL4)/MINL4, IIF(C5 <= O5 OR C5 <= H6, 100*(MAXH5 - MINL5)/MINL5, IIF(C6 <= O6 OR C6 <= H7, 100*(MAXH6 - MINL6)/MINL6, IIF(C7 <= O7 OR C7 <= H8, 100*(MAXH7 - MINL7)/MINL7, IIF(C8 <= O8 OR C8 <= H9, 100*(MAXH8 - MINL8)/MINL8, IIF(C9 <= O9 OR C9 <= H10, 100*(MAXH9 - MINL9)/MINL9, 100*(MAXH10 - MINL10)/MINL10))))))))))

You can shorten this slightly by taking both the 100 * and the denominator outside the nested IIF statements.

100 * (IIF(C <= O OR C <= H1, 0, IIF(C1 <= O1 OR C1 <= H2, H / L, IIF(C2 <= O2 OR C2 <= H3, MAXH2 / MINL2, IIF(C3 <= O3 OR C3 <= H4, MAXH3 / MINL3, IIF(C4 <= O4 OR C4 <= H5, MAXH4 / MINL4, IIF(C5 <= O5 OR C5 <= H6, MAXH5 / MINL5, IIF(C6 <= O6 OR C6 <= H7, MAXH6 / MINL6, IIF(C7 <= O7 OR C7 <= H8, MAXH7 / MINL7, IIF(C8 <= O8 OR C8 <= H9, MAXH8 / MINL8, IIF(C9 <= O9 OR C9 <= H10, MAXH9 / MINL9, MAXH10 / MINL10)))))))))) - 1)

Bruce,

You are brilliant, as always.

Thanks,
Derek

You're welcome.

I really don't know what you want for the signal considering the formula in question. It is designed to return numbers for a reason. If you want to convert it into a signal of sort, you would need to specify what comparison you want to apply to those numbers to generate a true or false result.

I suppose if you just want a condition formula for the green day itself as defined by derekb1959 in the original question, it would be the following.

C > O and C > H1