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.
In a cell in row 3:
=IF(AND(A3<=A2, A3<A4), XMATCH(A3, A4:A$10000,, -1)-1, "")
Fill down.
I am not sure if the formula is working right, it is giving me that rows where values match. But not the row of the most recent match. for example if Row A5 has the value of 4.260 it is matching this to Row 4559 which also have the value of 4.260.
I think the matching range A4:A$10000 is the key variable. It needs to be x rows away from the target cell and that is the challenge in what I am trying to do. Its to get to the value <= target value within a certain number of rows away, and not the whole list of values.
Thanks again.
GiGi
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Thanks HansVogelaar
This is a small set of the data and I have added comments to show what I am hoping for.
In Columns A-D you have the prices of each of the gas grades.
In column E you have the Date
In Column F is a deflection Up when prices go up from day to day, No change and Down when prices go down from day to day.
Column G is the day of the week,
Columns H and I are by how many dollars and what percentage was the change.
In Column J I identify the Target Price I am looking for. It is when there is a change (up or down) in the price. The text is colored to match to the row I am trying to find.
Column K has remarks explaining what is going on.
Column L has the result that I am looking for. It is the number of days between the two rows (the Target Price row and the row when the price becomes <= to Target price)
In Column M is your formula. It seems its working some of the time but less 1 so for example if you look at Target Price G (Row 67) your formula shows 4 but it needs to be 5. And I am not sure why other instances are not working and showing #N/A
Hope this helps.
Gigi
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.
