How to assemble Offset function with Condition?

Question

I Have date data in Column A and values data in Column B.

My aim goal is that for each date in column A : look if one value (column B) during 1 year is less than 90% of the corresponding Value of the date.

This is my data:

Output example :

I start at the first date (31/12/1986) and see the 900.82 value. I calcul 900.82 * 0.9 = 810.73 So i look if value in line 3 is inferior to 810.73,

• If not

I look if value in line 4 is inferior to 810.73, line 5, 6, 7 ,8..... during 1 year (that mean I stop this the 31/12/1987).

• If it is

I stop and i look the next date (01/01/1987) and do the same.

I am agree to translate it in VBA code, but it could be better on Excel.

I first tried an algo to understand the problem :

-Look the first date in column A
-Look the corresponding value in Column B and save it as Value
-Offset to the next date in column A
-Look the corresponding value in Column B and save it as Range.to.compare
-If Range.to.compare < Value * 90% then write "ok" in Column C
-Else, Offset to the next date in column A...
-Untill 1 Year (Untill Year(Date in Column A)<Year(Date in Column A) +1

I try to translate it in Excel or VBA.

This this what I tried by Excel :

But it works only for 1 day instead of 1 year

Answer 1

I think this is what you're after. I broke the calculation into multiple columns to show the steps.

First I calculate the 90% value, in column C.

Column D is where most of the magic happens. In column D I find all rows that have a date < the current date + 1 year, and get the value (Col B) for each of those rows. I then take the minimum of all those values. Note this is an array formula so you need to press shift-ctrl-enter after you type it in.

Now that I have the minimum from the each range, I just need to compare that with the 90% value.