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.
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!
GiGi
The 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'!