suming multiple rates

[nba]

I have 2 sheets. One with 365 dates showing low, mid or high season. The
second sheet has 3 tables listing multiple units with different pricing for
each day of the week for each season. EG:

table 2 rates low season
sun mon tue wed thu fri sat
unit 1 100 90 90 90 90 100 100
unit 2 150 100 100 100 100 150 150

I am using
VLOOKUP(H9,INDIRECT(VLOOKUP(F11,Dates,2,FALSE)),WEEKDAY(D11)+2,FALSE) to find
the right rate for the unit (H9) for the right date but now i need to add
each night of the booking for example 5 nights starting wed in unit 1.

that would be 90+90+100+100+100=480

can you help.

 

[Joel]

I fwouold use two sumproducts. I'll put rows and column into your data to
show you how

A B C D E F G H
1 sun mon tue wed thu fri sat
2 unit 1 100 90 90 90 90 100 100
3 unit 2 150 100 100 100 100 150 150


find the minum rates from row 2 comparing with row 3

=Sumproduct(--(B2:H2<=B3:H3),B2:H2)

now the same in 2nd row. I'm only using less than

=Sumproduct(--(B3:H3<B2:H2),B3:H3)

Now put the two formulas into one
=Sumproduct(--(B2:H2<=B3:H3),B2:H2) + Sumproduct(--(B3:H3<B2:H2),B3:H3)

 

[nba]

Joel,

what i am after is the sum of multiple days booking in one unit. in this
example a booking for 5 nights in unit 1 starting wed what is the total? eg:
unit = a2, days = e2+f2+g2+h2+b2=480
The start day changes all the time with each new booking so I am not sure
how to loop arround through the days of week. Your help is appreciated.

 

[Joel]

The best way is with a UDF function. Try this

=GetPrice(A1:H3,1,A7,B7)

A1:H3 is the table range including the header row and header column like you
would use in a vlookup.

The 1 indicates the row offset from the header. With two diffferent units
it would be iehter a 1 or a 2.

A7 is the StartDate as a real date in the worksheet like 8/15/09

A8 is the End Date as a real date in the worksheet like 8/25/09

The UDF is the following macro

Function GetPrice(PriceTable As Range, TableOffset As Integer, _
StartDate As Date, EndDate As Date)
GetPrice = 0
For MyDate = StartDate To EndDate
Wday = Format(MyDate, "ddd")
Set c = PriceTable.Rows(1).Find(what:=Wday, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
GetPrice = GetPrice + c.Offset(TableOffset, 0)
End If

Next MyDate

End Function

 

[Domenic]

For simplicity, let's assume the following...

A1:H3 contains the table for the low season

A5:H7 contains the table for the mid season

A9:H11 contains the table for the high season

J1:J365 contains the date for each day of the year

K1:K365 contains the corresponding season (Low, Mid, or HIgh)

M1 contains the date of interest, such as 8/26/2009

N1 contains the number of nights, such as 5

O1 contains the unit number, such as Unit 2

First, define the following...

Insert > Name > Define

Name: Low

Refers to: =$A$1:$H$3

Click Add

Name: Mid

Refers to: =$A$5:$H$7

Click Add

Name: High

Refers to: =$A$9:$H$11

Click Ok

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(VLOOKUP(O1,CHOOSE(MATCH(VLOOKUP(M1,J1:K365,2,0),{"Low","Mid","High"}
,0),Low,Mid,High),WEEKDAY(M1+ROW(INDIRECT("1:"&N1))-1)+1,0))

 

[pshepard]

Hi nba,

1. Name three ranges: "Low", "Medium", "High"

Example of the "Low" range (8 columns, as many rows as there are units):

sun mon tue wed thu fri sat
unit 1 100 90 90 90 90 100 100
unit 2 150 100 100 100 100 150 150

2. Name range "Calendar_365_L_M_H" (all 365 rows, 2 columns)

Example of this range:

9/1/2009 Low
9/2/2009 High
9/3/2009 Medium
9/4/2009 Medium
9/5/2009 Medium
9/6/2009 Low
9/7/2009 High

Range Name Example of data

"Unit" "Unit 1"
"Check_in" 9/2/2009
"Check_out" 9/7/2009

in an empty cell -

a10: =check_in
a11:=IF(A10="","",IF(A10+1>check_out-1,"",A10+1))
copy cell A11 down to enough rows that will cover the number of days
that the most a reservation will last for
b10:=IF(A10="","",VLOOKUP(Unit,INDIRECT(VLOOKUP(A10,Calendar_365_L_M_H,2,FALSE)),WEEKDAY(A10)+1))
copy cell b10 down to enough ros that will cover the number of days
that the most a reservation will last for.

Sum(b10:b#####)

This will give you the amount for the reservation.

Hope this helps,

Peggy

 

[nba]

Domenic,

I used your formula shown below and it work whilst the booking date is in
one season, but when the season changes mid booking the formula is not
changing over to the different rate table.

eg; a 5 night booking starting 30 nov and ending 5 dec crosses 2 seasons.

date: 30/11 1/12 2/12 3/12 4/12
season: low high high high high
price: 110 150 150 150 165

Totals = 725 your result is 565

each day of the booking needs to be checked for the season as this changes
and effects the totals.

 

[Domenic]

Based on the same assumptions and defined names, try the following...

For the low season portion....

P1, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(ISNUMBER(MATCH(M1+ROW(INDIRECT("1:"&N1))-1,IF(K1:K365="Low",J1:J3
65),0)),VLOOKUP(O1,Low,WEEKDAY(M1+ROW(INDIRECT("1:"&N1))-1)+1,0)))

For the mid season portion...

Q1, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(ISNUMBER(MATCH(M1+ROW(INDIRECT("1:"&N1))-1,IF(K1:K365="Mid",J1:J3
65),0)),VLOOKUP(O1,Mid,WEEKDAY(M1+ROW(INDIRECT("1:"&N1))-1)+1,0)))

For the high season portion...

R1, confirmed with CONTROL+SHIFT+ENTER:

=SUM(IF(ISNUMBER(MATCH(M1+ROW(INDIRECT("1:"&N1))-1,IF(K1:K365="High",J1:J
365),0)),VLOOKUP(O1,High,WEEKDAY(M1+ROW(INDIRECT("1:"&N1))-1)+1,0)))

For the total...

S1:

=SUM(P1:R1)