+ Reply to Thread
Results 1 to 20 of 20

Calculate price of hotel room according to season

  1. #1
    Registered User
    Join Date
    04-18-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    7

    Calculate price of hotel room according to season

    Hello,

    As an assignment I need to be able to calculate the price of a hotel room according to what season it's in (high, mid, low) but the seasons differ by date.
    For example: A room booked from the 10th of April to the 17th of April costs 100 euros a day for high season and 75 euros a day for mid season. The days from the 10th of April to the 13th of April are high season (100 euros) and the days from the 14th to the 17th are mid season (75 euros).

    I don't know how to do this as the days that are high, mid or low season change day by day (18th of April to 20th is low season, 21st to 22nd mid, 23rd to 27th low again). Is there any way to do this efficiently or a way to do this at all?

    This is an image showing the high, mid and low seasons. High is orange, mid is gray, low is white. (http://i.imgur.com/Wq3MrFx.jpg)

    Thank you in advance,

    Shadir

  2. #2
    Registered User
    Join Date
    01-19-2017
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    92

    Re: Calculate price of hotel room according to season

    Sounds like you need a structured IF statement for this, but you mentioned this was an assignment (as in homework)?

  3. #3
    Registered User
    Join Date
    04-17-2017
    Location
    New York, USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Calculate price of hotel room according to season

    Shadir,

    You can create a list in a separate tab for the foreseeable future dates and another column for entering the rate per day for high, mid, low season

    e.g.:

    Column 1 Column 2

    Apr 18, 2017 75
    Apr 19, 2017 75
    Apr 20, 2017 75
    Apr 21, 2017 100
    Apr 22, 2017 100
    Apr 23, 2017 50
    ...and so on

    In another tab where you are calculating the rate, you can enter Start date in one cell and end date in an adjacent cell and then use a sumifs formula to calculate total tariff
    Total tariff = sumifs(column 2, column 1, ">="&Startdate, column 1, "<="&Enddate)

    Let me know if any of this is unclear or unhelpful.

    AK

  4. #4
    Registered User
    Join Date
    04-18-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    7

    Re: Calculate price of hotel room according to season

    It's an assignment as a group project. The assignment is to make multiple excel worksheets for the hotel and this part is the part we are struggling with.

    @Ak, thank you very much for that, will definitely give it a go.

    Shadir
    Last edited by Shadir; 04-18-2017 at 02:04 PM.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate price of hotel room according to season

    See the attached:

    Create table in A:B showing H, M, L for each date

    in F2

    =SUMPRODUCT(($A$2:$A$366>=D2)*($A$2:$A$366<=E2)*(($B$2:$B$366="H")*100+($B$2:$B$366="M")*75+($B$2:$B$366="L")*50))

    D2= Start Date

    E2=End date

    C105:c118 contain room rates to enable checking of formula.

    Low season is 50 Euros.

    Attached version with "Lookup" table for Room Rates. (highlighted in yellow)

    =SUMPRODUCT(($A$2:$A$366>=$D$2)*($A$2:$A$366<=$E$2)*(($B$2:$B$366="H")*VLOOKUP("H",Room_Rates,2,0)+($B$2:$B$366="M")*VLOOKUP("M",Room_Rates,2,0)+($B$2:$B$366="L")*VLOOKUP("L",Room_Rates,2,0)))
    Attached Files Attached Files
    Last edited by JohnTopley; 04-18-2017 at 02:31 PM.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Calculate price of hotel room according to season

    Another point of view
    see att.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate price of hotel room according to season

    A variant on Sandy's response:

    Sheet "TariffS" has tables with tariffs: the column table columns AJ:AK is used in the calculations.

    Column K is created from first table:

    in AK2

    =INDEX(Tariffs!$B$2:$AF$25,MATCH(EOMONTH($AJ2,-1)+1,Tariffs!$A$2:$A$24,0)+1,ROWS($1:1)-VLOOKUP(EOMONTH($AJ2,-1)+1,Tariffs!$A$2:$AG$25,33,0))

    In "Bookings"

    in E2

    =COUNTIFS(Dates,">=" &$B2,Dates,"<=" &$D2-1,Tariff,E$1)

    Copy across to G

    in H2 ("Price)

    =SUMPRODUCT(($E$1:$G$1=$K$2:$K$4)*($L$2:$L$4)*($E2:$G2))

    Used named range "Dates".
    Attached Files Attached Files
    Last edited by JohnTopley; 04-19-2017 at 04:00 AM.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Calculate price of hotel room according to season

    John, we have not enough infomation from OP because on tariffs tab cannot be defined more than one room in the same, or similar, time., eg. room for standard client (14-27 April) and for vip (eg. 10-20 April).

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate price of hotel room according to season

    @Sandy,

    My reply (and i am sure yours too) is a "model" approach.

    If there are tariffs per room "category" e.g Single/Double , Standard, De Luxe, ..etc .. then the solution could easily be extended.
    Last edited by JohnTopley; 04-19-2017 at 06:41 AM.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Calculate price of hotel room according to season

    @John,
    That is why I didn't defined tariffs tab because I've not enough info.
    And you're right with the "model".

  11. #11
    Registered User
    Join Date
    04-18-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    7

    Re: Calculate price of hotel room according to season

    @John
    Those are some very nice examples, I will have to look around a bit on how to implement the full table into it.
    As for giving more information, the tariffs of the rooms per category are in this image: http://i.imgur.com/99EbYRb.png

    It's in Dutch so I'll provide some more of an explanation.
    The 3 light grey bars are low, mid and high season (top to bottom)
    Within every season you have 3 types of room you can get, which is the categories in between the grey bars going top to bottom: Room + breakfast, half board and full board.
    On top there are different categories of clients: Adult, Children 6 - 14, Children 2 - 5, Children > 2

    This is a lot more than just the room tariffs so I will have to try to find a way to extend the solution. You have helped me a long way already, if possible and it doesn't take too much time it would be great to see a solution using the things mentioned here but your help so far is very appreciated.

    I love the work you did with the Room_Bookings document, it looks great. If you could show me how to make that style with this new information I could use it for future reference.

    Shadir

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate price of hotel room according to season

    If you can post a file (maybe mine with the updated tariff table) and some examples of the room categories/client categories calculations , then I will look to update the file.

    I did look at the original image but was too idle to input the data into my table!!!

    With regard to the second image: can please provide that is table in Excel. It is very easy to "lookup" such a table given we have Room/Client categories.
    Last edited by JohnTopley; 04-19-2017 at 10:54 AM. Reason: Added more info

  13. #13
    Registered User
    Join Date
    04-18-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    7

    Re: Calculate price of hotel room according to season

    I have added the tariffs for the remaining dates that weren't filled in yet, added a table with the price for each tariff and category and 3 examples of what a booking could be.

    I hope this is sufficient. As I didn't know how to add an attachment I uploaded it to my dropbox on this link: https://www.dropbox.com/s/oil73dmked...ings.xlsx?dl=0

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Calculate price of hotel room according to season

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  15. #15
    Registered User
    Join Date
    04-18-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    7

    Re: Calculate price of hotel room according to season

    I have added the attachment. Under examples the first things is how it's filled in. The booking is added, they choose which type of room, fill in dates, fill in the amount of each type of person and the price is calculated according to the tariffs.
    Under that there are 3 examples of what the prices would look like.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate price of hotel room according to season

    I had a play myself so see the attached; you can update the "Tariff" tab with your version posted in your last file.

    I added a "Prices" tab.

    In "Bookings"

    in M2

    =SUMPRODUCT((Prices!$C$2:$C$10=Bookings!$B2)*(Prices!$D$1:$F$1=Bookings!$I$1)*(Prices!$D$2:$F$10)*(($F$1=Prices!$B$2:$B$10)*$F2+($G$1=Prices!$B$2:$B$10)*$G2+($H$1=Prices!$B$2:$B$10)*$H2))*$I2

    other formulas are same with appropriate ranges chages
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    04-18-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    7

    Re: Calculate price of hotel room according to season

    That is absolutely incredible, thank you very much. I can use this now and for future reference by changing names and tariffs, I appreciate the effort and help. And I also know a bit more about excel thanks to you guys.

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Calculate price of hotel room according to season

    You are very welcome. Please feel free to come back to the forum if you need further help.

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

  19. #19
    Registered User
    Join Date
    03-16-2021
    Location
    Jakarta
    MS-Off Ver
    2007
    Posts
    1

    Re: Calculate price of hotel room according to season

    May I download for study material

  20. #20
    Registered User
    Join Date
    04-18-2017
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    7
    Quote Originally Posted by agung_r123 View Post
    May I download for study material
    Should be perfectly fine to do so

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel 2013 wants to calculate Sales Price , If cost price exits and wants change SP
    By Bitto in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2015, 12:49 PM
  2. Calculate medians by season
    By MAHood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2014, 03:55 PM
  3. [SOLVED] Multiple IF's to calculate price based on cost and add handling charge and round price
    By RoyRose in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-13-2014, 01:18 PM
  4. [SOLVED] Hotel Rooming List, sorted by category of room (Single Double etc)
    By Robert.Appel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-02-2014, 06:17 AM
  5. [SOLVED] Formula to calculate price for hotel room upgrades
    By Uplate in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 03-13-2013, 03:02 AM
  6. Calculate a total holiday price based upon different nightly room rates
    By bellevue in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-18-2011, 04:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1