Create Formula For Rooming List

C

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
 
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.
 
NBVC said:
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.
Click to expand...

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
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
NBVC said:
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.
Click to expand...

This is great. I works. Thank you! I am learning Excel.
 
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?
 
canard2 said:
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.
Click to expand...
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?
Click to expand...

What if the room is rented for 1 or 2 nights? Is the 4 night min still in effect?
 
NBVC said:
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.
Click to expand...

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 ????
 
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:
NBVC said:
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.
Click to expand...

Great. This helps. Thanks.
 
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
 
I would like you to update the sample with some relevant data, and show expected results.
 
NBVC said:
I would like you to update the sample with some relevant data, and show expected results.
Click to expand...

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)
 
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.
 
You must log in or register to reply here.
Back
Top