Create Formula For Rooming List

canard2

New member
Joined
Dec 3, 2013
Messages
7
Reaction score
0
Points
0
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.

Hotel06/1207/1208/1209/12Pre paidPay onsite
Hotel A1111Price for 07/12 + 08.12 + 24.20€Price for 06/12+09/12
Hotel B11Price for 07/12 + 08.12 + 24.20€

Thanks
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

canard2

New member
Joined
Dec 3, 2013
Messages
7
Reaction score
0
Points
0
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
 

Attachments

  • List for Test.xlsx
    21.8 KB · Views: 106

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

Attachments

  • List for Test.xlsx.XLS
    21.8 KB · Views: 100

canard2

New member
Joined
Dec 3, 2013
Messages
7
Reaction score
0
Points
0
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.
 

canard2

New member
Joined
Dec 3, 2013
Messages
7
Reaction score
0
Points
0
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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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?
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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 ????
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I might have added the wrong attachment. :embarassed: 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.
 

Attachments

  • Copy of List for Test.xlsx
    19 KB · Views: 557
Last edited:

canard2

New member
Joined
Dec 3, 2013
Messages
7
Reaction score
0
Points
0
I might have added the wrong attachment. :embarassed: 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.

Great. This helps. Thanks.
 

canard2

New member
Joined
Dec 3, 2013
Messages
7
Reaction score
0
Points
0
HelloI have another request. For the same spreadsheet.
I have different hotels. What formula can I use to see how many rooms I have left for my rooming list?

Cheers
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I would like you to update the sample with some relevant data, and show expected results.
 

canard2

New member
Joined
Dec 3, 2013
Messages
7
Reaction score
0
Points
0
I would like you to update the sample with some relevant data, and show expected results.

Hi I found the solution using SUMIFS. This is the solution I used (in words) = =Roomblock-(SUMIFS(Column P(which is my 1st night),Column O (attributed hotel, Name of hotel) and it worked.

I wanted to just write the name of hotel but was unable to create the formula when the hotel had 2 words. Does the formula only accept one word (ex; =$AF$2-(SUMIFS(S$3:S$66,$O$3:$O$66,"catalonia")) -> this works but it I do =$AF$2-(SUMIFS(S$3:S$66,$O$3:$O$66,"Hilton NYC")) It does not work (I tried underscore, no space etc)
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
That should work as long as there is an exact match. Make sure that your "match" in column O doesn't have extra space at the end or in between words.
 
Top