Calc total cost based on room rates

Hi all,

I have just started working at a backpacker hostel and need some help updating an excel sheet that they use. I need quite a complicated IF formula, I believe.

It is for their payment spreadsheet and the necessary columns are as below;

E F G H
Room No. Nights No. Persons Total


There are different prices for the different rooms as follows
Room Numbers 2, 3, 4, 6, 8 and 10 are $39
room 1 is $16
room 5 is $15
room 7 is $12

what we need in column H is a formula that will take the room number, figure out which price it is, multiply it by number of nights, also multiplied by number of people, and finally multiply the total price by 45 to display it in local currency...

the other factor is that rooms 2,3,4,6,8 and 10 are $39 regardless of how many people are in it, whereas rooms 1, 5 and 7 are priced per person, so THOSE rooms need to multiply by number of persons in column G.

So as an example;


E F G H
Room No. Nights No. Persons Total
1 2 1 $1,440
5 14 1 $9,450
2 7 2 $12,285
7 6 2 $6480


I know it is a really complicated one, but if it is possible to create a formula for it that would help out a lot otherwise we have to keep manually calculating the price each time.

I hope someone can help!

Thank you!

Rachelle, welcome to the forum

For future reference, please title y6our threads with something that describes your problem, not what you thing the solution might be. (I have changed it for you - this time)

You probably need to use VLOOKUP(), but can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Try this:

Please Login or Register  to view this content.

where E2=1, pick 16*G2 (price*persons), E2=2, pick 39,...,E2=5, pick 15*G2,...then multifly with night no and 45

Hi Rachelle and welcome to the forum,

I have two answers for you. One is using the Choose() function like bebo above and the other is closer to Alan's. Examine both in the attached table and see if you want to use Choose() or VLookup()

Try this, copied down (Im sure someone will simplify it for you)...
=(B2-A2)*45*IF(VLOOKUP(E2,$C$10:$D$19,2,0)=39,39,VLOOKUP(E2,$C$10:$D$19,2,0)*G2)