calculate additional fees based on multiple criteria!!!

[MandyG626]

Hi all!

So, I'm both an idiot in regards to Excel and Forum posting (as this is my first foray in the latter).

Basically, what I'm trying to do is this:

If a hotel has multiple "Seasons" (example: high, low, shoulder) in any given year, this affects pricing. I have already run a master template to calculate the double occupancy price based on the season and the room type chosen.

HOWEVER, there are additional fees for extra adults...and of course the fees vary depending on BOTH the season and the room type. I tried the below formula...presuming A1 is room type, B1 is Season, and C1 is number of adults (with base of 2 per room)...

=IFS(B1="Low2018",A1="Resort",C1="3",94)

Basically, I'm trying to say, the Resort room, in Low Season for 2018, with 3 adults, incurs an additional $94 charge. (I've already calculated the double occupancy, remember, so I'm just trying to add this in as a separate column).

But nothing is working. If the formula doesn't return an error, it also doesn't return the $94 addition I'm looking for...

Like I said, I'm an idiot. And I'm grateful for anyone who can help me out here!

 

[NARAYANK991]

Hi ,

The syntax for your formula would probably be :

=IF(AND(A1 = "Resort" , B1 = "Low2018" , C1 = 3) , 94 , 0)

I am assuming that your specification of Low season in 2018 will be by entering Low2018 in cell B1.

C1 should contain a numeric value , which is why the check is for C1 = 3 and not C1 = "3".

 

[mrshl9898]

Like this?

=IF(AND(B1="Low2018",A1="Resort",C1="3"),94,"")

 

[MandyG626]

You are literally my favorite person in the word right now!!! I just plugged in it and it worked. (Thank goodness...I won't even talk about how long I spent scouring the internet for something that would work before giving in and posting my question to a forum.

Thank you SO much!!!!

 

[Peter_SSs]

Welcome to the MrExcel board!

I see that you have a solution to your specific problem, but I'm thinking that you are going to have to have similar formulas for different seasons/years, different room types and different number of adults.
Could something like this be any use to you where you have a lookup table somewhere (mine is in columns G:M) that contains all the additional values per one extra person and then a formula like I have in cell E2, copied down, will calculate the extra no matter what combination of year/season/room-type/number of people is chosen.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Type	Season	Year	Adults	Extra			2017			2018
2	Resort	Low	2018	3	94		Type	High	Shoulder	Low	High	Shoulder	Low
3	Std	Shoulder	2017	4	180		Resort	115	95	90	120	100	94
4	Resort	High	2018	2	0		Std	100	90	80	110	98	90
5	Resort	High	2018	3	120
Additional Amount

Cell Formulas
Range	Formula
E2	=INDEX(H$3:M$4,MATCH(A2,G$3:G$4,0),MATCH(C2,H$1:M$1,0)+MATCH(B2,H$2:J$2,0)-1)*(D2-2)