# Thread: Create Formula For Rooming List

1. ## Create Formula For Rooming List

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. 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. Originally Posted by NBVC
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

4. 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.

5. Originally Posted by NBVC
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. Your welcome. Excel is GREAT!

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. Originally Posted by canard2
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. Originally Posted by NBVC
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. 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.

Page 1 of 2 1 2 Last

#### Posting Permissions

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