Calculate % trend reversal from last bank high

[RINCONPAUL]

In the attachment I have col A with bank balance values. In col B, I'm after a formula or code to evaluate if the current cell in col A is a new bank high "Bank high" and then if the following cell values are less than the previous bank high, calculate the negative % of decline, until a new bank high is achieved. Then that value is the next benchmark for all following cells to reference.

Cheers

[SamT]

I can do it with VBA. In fact I can show trend reversals from high to low and low to high. I am not sure that is what you want, In the example you only show the latest Highest value.

the Bank in Rows 10 to 12 show a steady increase, then Row 13 starts falling again, but your next Bank High is not until the Bank is higher than row 8, the previous Bank High. This means it is possible that you will never again see a Bank High.

It is possible to incorporate a Cutoff value such that until it rises or falls x% the trend is not considered to be reversed. This would show the Trends in Column B to pretty much be in accordance with the visuals in the chart. It might not show the humps at 12 and 25, but that can be adjusted by changing the Cutoff value.

And of course, there are many people here who are much smatrter than I at this kind of thing. I am sure one will show up before too long

[RINCONPAUL]

I can do it with VBA. In fact I can show trend reversals from high to low and low to high. I am not sure that is what you want, In the example you only show the latest Highest value.

the Bank in Rows 10 to 12 show a steady increase, then Row 13 starts falling again, but your next Bank High is not until the Bank is higher than row 8, the previous Bank High. This means it is possible that you will never again see a Bank High.

It is possible to incorporate a Cutoff value such that until it rises or falls x% the trend is not considered to be reversed. This would show the Trends in Column B to pretty much be in accordance with the visuals in the chart. It might not show the humps at 12 and 25, but that can be adjusted by changing the Cutoff value.

And of course, there are many people here who are much smatrter than I at this kind of thing. I am sure one will show up before too long

Thanks for your reply SamT.
The main purpose of my inquiry is to assess longterm uptrend lines generated by betting systems. A monte-carlo scenario generates random outcomes using the same rules. I then would look for the biggest trend reversal that occurs for the simulations undertaken and assess whether the staking for the system is too high and therefore too risk adverse. To answer part of your question, the % decline should always be relative to the last bank high. Working out the % increase in bank, from the previous high, is of no consequence. The drawdown on funds as a percentage of bank is the critical factor.

[SamT]

The attached file works for me. Try it after changing the values in column "A". Note that if you change the column Layout, you will have to edit the code in VBA, but it is very simple and straight forward.

[RINCONPAUL]

Nice work SamT, works a treat. Thanks so much. Now I just put a '=MIN(Range) in a cell and I've got the largest% trend reversal.

[RINCONPAUL]

Whoops, I might've spoke too soon SamT. It worked OK for that sample range, but when I put a real world range of 3k+ in, it just hung, and the Excel cpu% skyrocketed to 80+%.

I've added the file with the longer range, if you wouldn't mind looking at it?

Cheers

[SamT]

My bad. I forgot to turn off Formula Calculating before the rest of the code ran. This version completed in about 1.5 seconds. In the old version, it was trying to recaculate the MIN every time any cell changed, then the code and the formula started interfering with each other And poor Excel went schizophrenic and lost its mind.

[RINCONPAUL]

Now you're talkin'! That's beautiful. I ran 4 simulations of same data and within seconds had four trend reversals: 1.56, 1.75, 2.35, 2.27%. If I do enough simulations, I can summarise a likely trend reversal and stake my liability accordingly.

Great work

[SamT]