Forum Discussion
Gas (Petrol) Prices and how they fluctuate…
Thanks. The formula
=IF(AND(A2<=A1, A2<A3), LET(r, (A3:A$73<=A2)*ROW(A3:A$73), m, MIN(IF(r>0, r)), IF(m=0, "", m-ROW(A2))), "")
returns the number of days from the last day before an increase to the first date after that with the same or a lower value.
Thanks. The formula
=IF(AND(A2<=A1, A2<A3), LET(r, (A3:A$73<=A2)*ROW(A3:A$73), m, MIN(IF(r>0, r)), IF(m=0, "", m-ROW(A2))), "")
returns the number of days from the last day before an increase to the first date after that with the same or a lower value.
This new formula seems to work just fine.
Would you please take a minute or two to explain how it works so that if anyone (and me) finds this thread they can get the maximum benefit.
Thanks again!
Gigi
The formula
=IF(AND(A2<=A1, A2<A3), LET(r, (A3:A$73<=A2)*ROW(A3:A$73), m, MIN(IF(r>0, r)), IF(m=0, "", m-ROW(A2))), "")
in row 2 first checks whether the value in column A is less than or equal to the previous value (so the value went down), and less than the next value (so the value will go up after this row). If not, the formula returns an empty string "".
It the two criteria are met, r is assigned the result of (A3:A$73<=A2)*ROW(A3:A$73).
A3:A$73 is all gas prices after that in row 2. A3:A$73<=A2 returns an array of TRUE/FALSE values: TRUE if the later value is less than or equal to that in row 2, FALSE otherwise. We want to find the first of those, if any. When we use TRUE/FALSE in a multiplication, TRUE acts as 1 and FALSE as 0.
So (A3:A$73<=A2)*ROW(A3:A$73) is an array of row numbers for rows where the value is less than or equal to that in row 2, 0 for all other rows.
Next, m is assigned MIN(IF(r>0, r)). This is the minimum of all the positive (non-zero) values in r.
If this minimum is 0, it means that there were no non-zero values. In other words, there were no rows with a value less than or equal to that in row 2. So IF(m=0, "", m-ROW(A2)) returns the empty string "".
Otherwise, m is the row number of the first row with a value less than or equal to that in row 2, and
m-ROW(A2) is the number of days in between.