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 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.
- Thank You Very Much HansVogelaar
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!
GigiThe 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.
My formula is rather more laboured than yours
. Still, the chart is kind of pretty!= LET( seq, SEQUENCE(COUNT(regular)), prior, DROP(VSTACK(regular, TAKE(regular,-1)), 1), next, DROP(VSTACK(@regular, regular),-1), minimum, (prior > regular) * (regular < next) * regular, location, IF(minimum>0, seq), MAP(location, minimum, LAMBDA(k,m, IF(k, IFERROR(XMATCH(m, DROP(regular, k),-1),""), "") )) )- Thanks PeterBartholomew1
This is very nice! Would you please take a minute or two of your time to explain how this is all working? I think it would be nice to have this chart but I don't know what it is being charted and how to maintain it if I need be.
Wonderful!
GiGiThe formula looks so different because I work with entire ranges rather than individual cells. In this case 'regular' is the name I have applied to column A of your data. 'Prior' and 'Next' are arrays containing the same data but pushed down a cell and up a cell respectively to make it easy to compare the daily price with that of adjacent days. That allowed me to build an array of locally 'minimum' values, together with a sequence number that provides their location.
Then the serious calculation. For each minimum I use its location to discard earlier data (DROP) and use XMATCH to identify the days elapsed before the price returns to the target value. There are other things I would do if I were going to use the formula myself (not least getting the names 'prior' and 'next' the right way round) such as declaring elements of the formula as Named Lambda functions, so that exactly the same functions could be applied to mid-grade, premium and diesel.
As for the chart, it is a simple line chart showing the 'regular' prices (UK prices are about 80% higher than yours btw) but with the days to return to target shown as a custom x-error bar. The chart pretty much takes care of itself provided the input data (should be a Table) and the results are calculated dynamically.
At the moment the way I calculate using Excel is pretty exotic but, one day, perhaps, it may be seen as boring and 'normal'!
I don't think I'll ever be able to write formulas like that...
