Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Create Formula For Rooming List

  1. #1

    Create Formula For Rooming List



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello I have a rooming list and need to identify what will be the total cost per person depending on the hotel they stay in and dates. Here is the information:
    I have 4 hotels each with a different price that we have pre-paid for the guest. If I add the name of the hotel to the row I would like it to generate the total cost of the room for that individual by automatically calculating it against the number of nights.
    On top of that I need to add 24.20 per room. We pre-pay the rooms, however some might extend their stay and this they will have to pay upon departure and this needs to be summed up in another column.

    Hotel 06/12 07/12 08/12 09/12 Pre paid Pay onsite
    Hotel A 1 1 1 1 Price for 07/12 + 08.12 + 24.20 Price for 06/12+09/12
    Hotel B 1 1 Price for 07/12 + 08.12 + 24.20

    Thanks

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample workbook instead showing more details... I.e. where the price comes from, and how do you know that 07/12 and 058/12 are prepaid and 06/12 and 09/12 are Pay onsite? And give more background if possible to help us understand better your request.


  3. #3
    Quote Originally Posted by NBVC View Post
    Can you post a sample workbook instead showing more details... I.e. where the price comes from, and how do you know that 07/12 and 058/12 are prepaid and 06/12 and 09/12 are Pay onsite? And give more background if possible to help us understand better your request.
    Hello - I attached the spreadsheet
    In purple are the pay on-site dates and in blue the pre paid nights (+ I have to include the 24.20 fee)
    I basically want to put the name of the hotel in Column G and it adds up all costs per what the guest requested.
    Let's say :
    Hotel A - Hilton = 100 per night
    Hotel B - Ramada = 150 per night
    Hotel C - Fairmont = 200 per night

    Thanks
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    See attached.

    I create a little table of Hotels vs. Nightly prices on the side in U1:V4.

    In P3 entered formula:

    =IFERROR(VLOOKUP(G3,$U$2:$V$4,2,0)*SUM($I3:$N3)+24.2,"")

    in Q3 entered formula:

    =IFERROR(VLOOKUP(G3,$U$2:$V$4,2,0)*SUM($H3,$O3),"")

    and in R3:

    =IF(G3="","",SUM(P3:Q3))

    each copied down.

    I entered some samples to show results.

    Also note, I am not sure but I think your formula in H3, copied across to O3 and down should be:

    =IF(AND($B3<=H$2,$D3>=H$2),1,0)

    in order to also include the date in row 2, as an end date.
    Attached Files Attached Files


  5. #5
    Quote Originally Posted by NBVC View Post
    See attached.

    I create a little table of Hotels vs. Nightly prices on the side in U1:V4.

    In P3 entered formula:

    =IFERROR(VLOOKUP(G3,$U$2:$V$4,2,0)*SUM($I3:$N3)+24.2,"")

    in Q3 entered formula:

    =IFERROR(VLOOKUP(G3,$U$2:$V$4,2,0)*SUM($H3,$O3),"")

    and in R3:

    =IF(G3="","",SUM(P3:Q3))

    each copied down.

    I entered some samples to show results.

    Also note, I am not sure but I think your formula in H3, copied across to O3 and down should be:

    =IF(AND($B3<=H$2,$D3>=H$2),1,0)

    in order to also include the date in row 2, as an end date.
    This is great. I works. Thank you! I am learning Excel.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Your welcome. Excel is GREAT!


  7. #7
    Just a quick update about this formula =IF(AND($B3<=H$2,$D3>=H$2),1,0) should not be $D3>=H$2 because it's a rooming list so I don't need it to take into account the departure date but the day before.

    Another question about this rooming list: From 22/02 to 27/02 guest have to pay a 4 night minimum even if they only sleep 3 nights. Ex. Line 33 only have 3 nights (this is why it turns red), he is aware that he has to pay 4 nights. Can I add a formula already in the Column P Formula or do I need to create another formula in another column?

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by canard2 View Post
    Just a quick update about this formula =IF(AND($B3<=H$2,$D3>=H$2),1,0) should not be $D3>=H$2 because it's a rooming list so I don't need it to take into account the departure date but the day before.
    I wasn't sure, just wanted you to be aware....


    Another question about this rooming list: From 22/02 to 27/02 guest have to pay a 4 night minimum even if they only sleep 3 nights. Ex. Line 33 only have 3 nights (this is why it turns red), he is aware that he has to pay 4 nights. Can I add a formula already in the Column P Formula or do I need to create another formula in another column?
    What if the room is rented for 1 or 2 nights? Is the 4 night min still in effect?


  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NBVC View Post
    See attached.

    I create a little table of Hotels vs. Nightly prices on the side in U1:V4.

    In P3 entered formula:

    =IFERROR(VLOOKUP(G3,$U$2:$V$4,2,0)*SUM($I3:$N3)+24.2,"")

    in Q3 entered formula:

    =IFERROR(VLOOKUP(G3,$U$2:$V$4,2,0)*SUM($H3,$O3),"")

    and in R3:

    =IF(G3="","",SUM(P3:Q3))

    each copied down.

    I entered some samples to show results.

    Also note, I am not sure but I think your formula in H3, copied across to O3 and down should be:

    =IF(AND($B3<=H$2,$D3>=H$2),1,0)

    in order to also include the date in row 2, as an end date.
    Hi
    When I open the attachment it complains about thr .XLS extension and appears identical to the post #3 attachment, and yet others haven't had a problem. Tried opening as .xlsx and same problem ????

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I might have added the wrong attachment. Here is the right one....

    also, if canard2 does require the 4 day min no matter the days stayed in that date range, then the P3 formula would become:

    =IFERROR(VLOOKUP(G33,$U$2:$V$4,2,0)*MAX(4,SUM($I33:$N33))+24.2,"")

    copied down.
    Attached Files Attached Files
    Last edited by NBVC; 2013-12-06 at 06:28 PM.


Page 1 of 2 1 2 LastLast

Posting Permissions

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