I have 3 columns. A and B are called MAX and MIN. They contain data showing the highest and lowest values over a given period of time (columns descending - most recent values at the top).
The third (“MAX SINCE”) tells me how many periods since the current MAX value was exceeded. In this example, C2 returns “4”, because “550” (in cell A2) is larger than the values of the previous 4 cells in column A, but not the fifth cell (A7) which contains “555”. This all works correctly.
Now, my problem: I want to add column D - LOWEST MIN - which searches the MIN column (B) for the lowest value in the last “n” cells, “n” being the value in the MAX SINCE column (C).
In this example, D2 should return “530”, which is the lowest value of the previous 4 periods in column B. D3 should return “535” etc. But I don’t know how to make it do this on it’s own.
This community has been so good helping me before and I am very grateful! I hope you can help me once again.
