Help required for rate check on hotel based on 3 criteria

**wendor** · 10-23-2015, 12:51 PM

Hello,

I want to do a price check on the reservations in a hotel.
I have the price catalogue and reservations have already checked out.

I have created the attached file, with three sheets:
One sheet is the price catalogue
One sheet is the price per room/day/adult/date
one sheet is the actual reservations, where i want to cross check our system price based on three criteria:

1) type of room
2) adults
3) seasonality - length of stay. I am facing a problem here, as for example, a price per room can change during the stay of a booking.

I am trying to work with match,index,and nested ifs with no success.

Any help would be greatly appreciated!

**wendor** · 10-25-2015, 09:29 AM

Any ideas or anyone that can help me?

**newdoverman** · 10-25-2015, 11:19 AM

On the Price Check worksheet cell F5 enter this ARRAY formula. It will fill down automatically because it is in a table. I don't know if this is a price per person or not so the value in I5 might be the multiplication of the following value by the number of persons...you don't indicate.

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

If the price from the above is multiplied by the number of persons.

Formula:

Please Login or Register  to view this content.

**wendor** · 10-25-2015, 02:45 PM

hello and thanks for your answer.

I have tried this formula, but it doesnt take into consideration, the seasonality, when a room price is changed.
For example, for guest 3, the amount is wrong.
Can you think of any other way as i want to sum the prices from the table, based on the duration of the stay, room type and adults.
I was thinking to create another table like the calender, and when i enter a booking to automatically put "x" and countifs, after matching the "x" with the appropriate cost.
I dont know how to design it though.

**newdoverman** · 10-25-2015, 03:25 PM

Seeing that you had a price for the room per day and have assigned rates for the room, I thought that you had already applied the seasonal rates to those prices. In addition, I don't understand your Price Catalogue. To me, if there is some kind of pricing involved with the dates, that should be calculated in the room price per day.

**newdoverman** · 10-25-2015, 04:11 PM

Seeing that you had a price for the room per day and have assigned rates for the room, I thought that you had already applied the seasonal rates to those prices. In addition, I don't understand your Price Catalogue.

I re-arranged the Price Catalogue so that it makes sense to me (maybe not correct) and applied this to the starting dates for each stay.

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

The Price Catalogue that I used looks like this:

	A	B	C	D
10	Room	DBL	JS	SUI
11	27/03/2015	€86	€91	€121
12	30/04/2015	€86	€91	€121
13	01/05/2015	€108	€113	€142
14	23/05/2015	€108	€113	€142
15	24/05/2015	€126	€131	€161
16	06/06/2015	€126	€131	€161
17	07/06/2015	€148	€164	€204
18	01/07/2015	€148	€164	€204
19	02/07/2015	€170	€182	€231
20	25/07/2015	€170	€182	€231
21	26/07/2015	€200	€212	€260
22	27/08/2015	€200	€212	€260
23	28/08/2015	€170	€182	€231
24	02/09/2015	€170	€182	€231
25	03/09/2015	€148	€164	€204
26	15/09/2015	€148	€164	€204
27	16/09/2015	€126	€131	€161
28	26/09/2015	€126	€131	€161
29	27/09/2015	€108	€113	€142
30	07/10/2015	€108	€113	€142
31	08/10/2015	€86	€91	€121
32	31/10/2015	€86	€91	€121

If the rate changes during a stay, do you apply the different rates? This calculation doesn't.

**wendor** · 10-25-2015, 04:46 PM

yes rate chages during a stay and different rate is applied.
for example for guest 3 at 24/05/2016, different rate is applied for the rest of the stay.

Is anyway to calculate this? to sum all the costs of the stay

**newdoverman** · 10-25-2015, 05:02 PM

If the Price Catalogue rates were applied to the Room Price Per Day, it would make more sense and cover more possibilities easily. As it is, the Price Catalogue does not have any calculations regarding the number of persons.

The logical thing in my opinion is to work out the Room Price Per Day worksheet to include seasonal changes.

**wendor** · 10-25-2015, 05:23 PM

Price Catalogue is more complex. I have applied the rates to the Room Price per Day. Please ignore the Price Catalogue Sheet.
Is it possible to match and sum up the cost of the stay of a booking, when rates change during the stay?

**newdoverman** · 10-25-2015, 08:07 PM

If you have updated the Room Price Per Day in the same range ('ROOM PRICE PER DAY'!H6:HR14) then this formula entered in 'Price Check'!F5 should make the correct sum for the total number of nights and guests per room.

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You should be able to copy your revised rates into this workbook (copy into the range that currently exists in the workbook) and have the formulae work.

**wendor** · 10-25-2015, 09:10 PM

thank you! i can work now with this!

**newdoverman** · 10-25-2015, 09:14 PM

Good!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**newdoverman** · 10-25-2015, 09:48 PM

Please delete.

Help required for rate check on hotel based on 3 criteria

LinkBack

Thread Tools

Rate This Thread

Display

Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Re: Help required for rate check on hotel based on 3 criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA MsgBox pop up required based on two criteria.

[SOLVED] Calculate rate and change rate based on total value reaches a target

[SOLVED] Check for duplicates from two sheets of data based on two criteria (2 columns)

Hotel Reservation Spreadsheet - check in & out dates listed - need total in house & occ %

[SOLVED] If AUD selected then no exchange rate required

check distance difference based on criteria

LOOPING: Faster rate of convergence required

Bookmarks

Bookmarks

Posting Permissions