calculate additional fees based on multiple criteria!!!

MandyG626

New Member
Joined
Oct 29, 2017
Messages
2
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! :)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

NARAYANK991

Board Regular
Joined
Jan 10, 2012
Messages
216
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".
 

MandyG626

New Member
Joined
Oct 29, 2017
Messages
2
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Type</td><td style=";">Season</td><td style="text-align: right;;">Year</td><td style="text-align: right;;">Adults</td><td style="text-align: right;;">Extra</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Resort</td><td style=";">Low</td><td style="text-align: right;;">2018</td><td style="text-align: right;;">3</td><td style="text-align: right;;">94</td><td style="text-align: right;;"></td><td style=";">Type</td><td style="text-align: right;;">High</td><td style="text-align: right;;">Shoulder</td><td style="text-align: right;;">Low</td><td style="text-align: right;;">High</td><td style="text-align: right;;">Shoulder</td><td style="text-align: right;;">Low</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Std</td><td style=";">Shoulder</td><td style="text-align: right;;">2017</td><td style="text-align: right;;">4</td><td style="text-align: right;;">180</td><td style="text-align: right;;"></td><td style=";">Resort</td><td style="text-align: right;;">115</td><td style="text-align: right;;">95</td><td style="text-align: right;;">90</td><td style="text-align: right;;">120</td><td style="text-align: right;;">100</td><td style="text-align: right;;">94</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Resort</td><td style=";">High</td><td style="text-align: right;;">2018</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">Std</td><td style="text-align: right;;">100</td><td style="text-align: right;;">90</td><td style="text-align: right;;">80</td><td style="text-align: right;;">110</td><td style="text-align: right;;">98</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Resort</td><td style=";">High</td><td style="text-align: right;;">2018</td><td style="text-align: right;;">3</td><td style="text-align: right;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:13.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Additional Amount</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=INDEX(<font color="Blue">H$3:M$4,MATCH(<font color="Red">A2,G$3:G$4,0</font>),MATCH(<font color="Red">C2,H$1:M$1,0</font>)+MATCH(<font color="Red">B2,H$2:J$2,0</font>)-1</font>)*(<font color="Blue">D2-2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,755
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top