Forum Discussion

GeorgieAnne's avatar
GeorgieAnne
Iron Contributor
Aug 04, 2024

Gas (Petrol) Prices and how they fluctuate…

Hello Excellers,

 

I hope you are doing EXCELlent ,

 

Ever since the "Conflict" in Ukraine I have been tracking the price of gas (petrol) as a project that I am interested in. And once you see the data you can imagine so many scenarios. OK enough lets talk about Excel.

 

In the image below I have the price per gallon of gas in Column A. The  Green means the price went down from the prior day, Yellow means no change in price from the prior day, and Red means the price increased from the prior day. The Dates are in Column F off the image.

 

What I would like to do is count the days it took to recover all the increases. For example on Row 899 the price increased. So the Target Price will be 3.088 (Row 898) Then on Row 903 the price started decreasing and we see that on Row 906 the price recovered to an equal amount (Row 900). And then on Row 909 the price is now <= to Target Price. 3.079 < 3.088 (Target Price) so all of the increases have been eliminated and now we are back to the last lowest price (and some more).

 

What Formula can I use to count the days it took the price to become <= the Target Price (3.088)

 

Thanks in Advance!

GiGi

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Aug 11, 2024

    GeorgieAnne 

    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.

    • GeorgieAnne's avatar
      GeorgieAnne
      Iron Contributor
      Aug 07, 2024
      Thanks HansVogelaar
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 07, 2024

        GeorgieAnne 

        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?

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 12, 2024

    GeorgieAnne 

    Do not panic!  I do not really expect you to adopt this style of programming Excel, but, in case you or some future reader of the thread are interested, I have taken the solution as bit further by introducing a Lambda function.

     

    All that appears on the worksheet is

    = DaysToReturnλ(regular)

= DaysToReturnλ(midGrade)

= DaysToReturnλ(premium)

= DaysToReturnλ(diesel)

    in cells P2:S2.  I have created the chart for diesel prices as well because more seems to be going on there.

    For completeness sake the formula, as shown within the advanced formula editor is

    /*
"= DaysToReturnλ(grade)
returns period before a value returns to a prior local minumum value"
*/
DaysToReturnλ
= LAMBDA(grade, 
    LET(
        seq, SEQUENCE(COUNT(grade)),
        minimum, LocalMinsλ(grade),
        location, IF(minimum <> "", seq),
        MAP(
            location,
            minimum,
            LAMBDA(k, m, 
                IF(
                    k, 
                    LET(
                        valuesToSearch, DROP(grade, k),
                        nextOccurrence, XMATCH(m, valuesToSearch, -1),
                        IFERROR(nextOccurrence, "")
                    ),
                    ""
                )
            )
        )
    )
);

/*
"= LocalMinsλ(values)
retains local minimum values whist replacing other values by blank"
*/
LocalMinsλ
= LAMBDA(values, 
    LET(
        prior,  DROP(VSTACK(@(+values), values), -1),
        next,   DROP(VSTACK(values, 0), 1),
        minima, IF((values <= prior) * (values < next), values, ""),
        minima
    )
);

    As you see, just as others work to make their Excel formulas more concise, I work to expand them, adding explanations and separating out areas of distinct functionality.

Resources