suming multiple rates
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+1check_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(A1 0,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" wrote:
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)),WE EKDAY(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.
|