1

Yesterday I have asked a similar question, yet this one has a rather advanced addition:

Let us say we have a dataset that consists of Hotel and Airline prices (From 1st to 31st of Jan). I would like to know what would be the "cheapest" trip of N days.

To find the price of the trip I need to include the prices for N consecutive days of the hotel, as well as airline prices for the 1st and last day of the trip.

The kind people in this forum has shown me how to find the cheapest N consecutive days of the hotel:

=LET(range,B2:AF2,
length,B5,
Running_Total,SCAN(0,range,LAMBDA(a,b,a+b)),
Sequence_1,SEQUENCE(1,COLUMNS(range)-length+1,length),
Sequence_2,SEQUENCE(1,COLUMNS(range)-length+1,0),
difference,INDEX(Running_Total,Sequence_1)-IF(Sequence_2,INDEX(Running_Total,Sequence_2),0),
MIN(difference))

The question now is: how to find the cheapest trip for N consecutive days that includes the airline prices also?

The example of the dataset is shown below:

Example of the dataset

01 Jan 02 Jan 03 Jan 04 Jan 05 Jan 06 Jan 07 Jan 08 Jan 09 Jan 10 Jan 11 Jan 12 Jan 13 Jan 14 Jan 15 Jan 16 Jan 17 Jan 18 Jan 19 Jan 20 Jan 21 Jan 22 Jan 23 Jan 24 Jan 25 Jan 26 Jan 27 Jan 28 Jan 29 Jan 30 Jan 31 Jan
Hotels 96.8 33.5 92.0 99.1 46.1 47.4 48.6 92.1 21.1 33.1 4.1 67.6 28.6 6.0 63.4 5.9 37.8 43.7 14.0 96.2 76.2 7.4 36.4 30.6 14.0 86.5 33.2 21.4 85.7 76.4 15.1
Flights 40.1 34.6 71.6 30.4 28.8 48.0 71.9 32.4 70.8 32.2 45.8 70.7 41.3 98.3 18.8 44.8 59.7 67.7 77.7 25.5 98.1 33.6 37.5 22.3 33.3 58.3 9.7 97.5 73.1 21.0 54.0
Number of days to spend: 4
Lowest hotel price for 4 days: € 88.4
Lowest price for 4 days including airline tickets: ???

EDIT - Current answers and additions: It is now possible to find hotel + airline prices with this formula (Thanks to @JvdV):

=MIN(BYCOL(FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)),LAMBDA(Col_Num,SUMIFS(B2:AF2,B1:AF1,">="&Col_Num,B1:AF1,"<="&Col_Num+(B5-1))+SUMIF(B1:AF1,Col_Num,B3:AF3)+SUMIF(B1:AF1,Col_Num+(B5-1),B3:AF3))))

Additionally, I managed to make a formula that finds the dates for which the prices are lowest (not sure if this is the best method though):

=XLOOKUP(B7,BYCOL(FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)),LAMBDA(Col_Num,SUMIFS(B2:AF2,B1:AF1,">="&Col_Num,B1:AF1,"<="&Col_Num+(B5-1))+SUMIF(B1:AF1,Col_Num,B3:AF3)+SUMIF(B1:AF1,Col_Num+(B5-1),B3:AF3))),FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)))
ZygD
  • 22,092
  • 39
  • 79
  • 102
Paul Pieless
  • 59
  • 8
  • 1
    So are you interested in both prices or the combined cheapest price? Which would be `155,3` in this case right? If you'd leave on jan-22? – JvdV Aug 21 '22 at 09:11

2 Answers2

3

Lowest price for a trip with hotel and flight:

enter image description here

Formula in B8:

=MIN(BYCOL(FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)),LAMBDA(a,SUMIFS(B2:AF2,B1:AF1,">="&a,B1:AF1,"<="&a+(B5-1))+SUMIF(B1:AF1,a,B3:AF3)+SUMIF(B1:AF1,a+(B5-1),B3:AF3))))

Btw, for just the hotel I'd use:

=MIN(BYCOL(FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)),LAMBDA(a,SUMIFS(B2:AF2,B1:AF1,">="&a,B1:AF1,"<="&a+(B5-1)))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 2
    That is a very elegant solution @JvdV, thank you! Did not think the formula could be fit in one line :) – Paul Pieless Aug 21 '22 at 11:18
  • out of curiosity, is it possible to get a date range of the minimum sum? I assume that if the starting date is found, it is rather easy to find an ending date. – Paul Pieless Aug 21 '22 at 12:48
  • @PaulPieless, yes very much so. Check what happens when you remove `MIN()`. If you now know this underlaying array, you can use `LET()` to capture it. Use xlookup to find the match and return date range. – JvdV Aug 21 '22 at 13:27
  • Your answer is the best way to do it. I might add something based on my previous answer just for completeness. – Tom Sharpe Aug 21 '22 at 14:47
  • @JvdV Xlookup worked! Didnt use Let() however, this is my formula: ``` XLOOKUP(B7,BYCOL(FILTER(B1:AF1,B1:AF1<=(AF1-B5+1)),LAMBDA(Col_Num,SUMIFS(B2:AF2,B1:AF1,">="&Col_Num,B1:AF1,"<="&Col_Num+(B5-1))+SUMIF(B1:AF1,Col_Num,B3:AF3)+SUMIF(B1:AF1,Col_Num+(B5-1),B3:AF3))),FILTER(B1:AF1,B1:AF1<=(AF1-B5+1))) ``` – Paul Pieless Aug 21 '22 at 15:27
2

Here's the other way of doing it - a bit verbose by comparison but at least it gives the same answer:

=LET(hotelRange,B2:AF2,
flightRange,B3:AF3,
length,B5,
runningTotal,SCAN(0,hotelRange,LAMBDA(a,b,a+b)),
sequence1,SEQUENCE(1,COLUMNS(hotelRange)-length+1,length),
sequence2,SEQUENCE(1,COLUMNS(hotelRange)-length+1,0),
sequence3,SEQUENCE(1,COLUMNS(hotelRange)-length+1,1),
cost,INDEX(runningTotal,sequence1)-IF(sequence2,INDEX(runningTotal,sequence2),0)+INDEX(flightRange,sequence3)+INDEX(flightRange,sequence1),
MIN(cost))

enter image description here

You don't really need three separate sequences - just one perhaps starting at 0 then add 1 or length to it:

=LET(hotelRange,B2:AF2,
flightRange,B3:AF3,
length,B5,
runningTotal,SCAN(0,hotelRange,LAMBDA(a,b,a+b)),
sequence,SEQUENCE(1,COLUMNS(hotelRange)-length+1,0),
cost,INDEX(runningTotal,sequence+length)-IF(sequence,INDEX(runningTotal,sequence),0)+INDEX(flightRange,sequence+1)+INDEX(flightRange,sequence+length),
MIN(cost))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Thank you for the answer @Tom! While JvdV's answer is a cleaner solution, yours makes it much easier to understand what is actually going on behind the scenes :) – Paul Pieless Aug 22 '22 at 19:17
  • That's good. I had a bit of tunnel vision and just saw the question as getting the largest sum of N adjacent elements from a range, while @JvdV spotted that you could use the dates with sumif/sumifs to do it a more concise way – Tom Sharpe Aug 22 '22 at 21:44