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! :)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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".
 
Upvote 0
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!!!!
 
Upvote 0
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
ABCDEFGHIJKLM
1TypeSeasonYearAdultsExtra20172018
2ResortLow2018394TypeHighShoulderLowHighShoulderLow
3StdShoulder20174180Resort115959012010094
4ResortHigh201820Std10090801109890
5ResortHigh20183120
Additional Amount
Cell Formulas
RangeFormula
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)
 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,601
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top