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,B5),
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:
| 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)))
CodePudding user response：
Lowest price for a trip with hotel and flight:
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)))))