IF, VLOOKUP? and SUM Help

[Ozbear]

I think I need a combination of the above but am not really sure and totally lost on how to achieve what I need, or if it is even possible.

I am trying to get a worksheet together to calculate the cost and sell of hotel rooms based on the contracted rate for any seasonality and if a surcharge needs to be added. The contracts usually have the season rates and then states when a surcharge is to be applied. What has to be loaded into the Database is actually the date ranges, from / to and the actual rate for the date range.

The idea of the worksheet in the first instance is to get a sheet with the dates and rates to be entered, the second instance is to then look at actually uploading this into a SQL Database.

Anyway any help with a formula that would do what I need, or told not possible, would be greatly appreciated.

Have attached an example and explanation that is hopefully understandable.

 

[Sajan]

Hello Ozbear,
Welcome to the forum.

First of all, kudos to you for taking the time to explain your requirements. Wish more posters were like you!

Attached is a revised worksheet with the following formula applied to columns H to T in the Calculations tab. Let me know if that is what you had wanted.
=IFERROR(SUMPRODUCT((RoomCategory=$C4)*(SeasonalRateToApply=LOOKUP(1, 1/(($D4>=DateFrom)*($E4<=DateTo)),SeasonalIndicator))*INDEX(RoomRates, 0, MATCH(H$2, RoomType,0)))+LOOKUP(1, 1/(($D4>=DateFrom)*($E4<=DateTo)),SeasonalSurcharge),"---")

I did not understand columns V to AG on the Calculations tab. Not sure if you were looking to add some calculations there also.

Cheers,
Sajan.

 

[Ozbear]

Sajan,

I can not say Thank You enough. I would never have been able to put this together ever.

again Thank You.

Ozbear

Hello Ozbear,
Welcome to the forum.

First of all, kudos to you for taking the time to explain your requirements. Wish more posters were like you!

Attached is a revised worksheet with the following formula applied to columns H to T in the Calculations tab. Let me know if that is what you had wanted.
=IFERROR(SUMPRODUCT((RoomCategory=$C4)*(SeasonalRateToApply=LOOKUP(1, 1/(($D4>=DateFrom)*($E4<=DateTo)),SeasonalIndicator))*INDEX(RoomRates, 0, MATCH(H$2, RoomType,0)))+LOOKUP(1, 1/(($D4>=DateFrom)*($E4<=DateTo)),SeasonalSurcharge),"---")

I did not understand columns V to AG on the Calculations tab. Not sure if you were looking to add some calculations there also.

Cheers,
Sajan.

 

[Sajan]

Hi Ozbear,
Thanks for the feedback! Happy to help!

Keep visiting this forum... and in no time you will be creating better formulas than the one above! That is what I did!

Cheers,
Sajan

 

[Ozbear]

A little more help if possible.

Another variable has been thrown into the mix. Some Hotels have a rate for Weekdays and different rate for weekends.

I figure that it is going to be too difficult to have the rates calculate on just one sheet and can work with having weekday rates on one sheet and the weekend on another, this is for uploading into a SQL database, but I wanted to add in some fields to indicate the days that are valid for.

Have added in 7 Columns for the week days and called array WeekDays, below this along side each room category i have added in a Y or N for the days the rate is valid and called array WeekOpDays, have also added in a column next to the Room category and called array WeekYorN to indicate if the rate is valid for 7 days or not. In additional i would like to have a 1 instead of the Y and 0 instead of the N.
What I have tried, based on what you sent originally, is the below but I am obviously got it wrong because I get ---.

=IFERROR((RoomCategory=$C4)*(WeekYorN=LOOKUP(1, 1/(($D4>=DateFrom)*($E4<=DateTo)),SeasonalIndicator))*INDEX(WeekOpDays, 0, MATCH(H$3, WeekDays,0)),"---")

Where have I gone wrong? Should I be looking at this from a different angle rather than IFERROR? Also how do I get a 1 or 0 instead of Y or N.

Happy to send the sheet again if it is easier.

Thanks,

 

[Sajan]

Hi Ozbear,
Yes, please upload the worksheet.

-Sajan.

 

[Ozbear]

Thanks

 

[Sajan]

Hi,
Can you add a couple of rows of manually calculated cells? I am not completely clear on what you are asking.

-Sajan.

 

[Ozbear]

Hi Sajan,

Have added in a few of the details I would like. the reason for this is that the record for the date range has a field for each day that it is available, I was just doing my SQL script to put the 1 all the time and then someone reminder me about weekend rates so would like, if possible, to have it to have a value for each day for each date range. Then I can have 1 sheet for a weekday and one for weekend and the appropriate days indicated, the days vary on the hotel, some have just Sat being more expensive and some have Fri and Sat.

Hope that attached is OK.

Thanks,

Am having trouble uploading the file, get an error message

Got it

 

[Jake Collins]

Dear Ozbear

I'm not entirely clear on what you want. does

=IFERROR((RoomCategory=$C4)*(SeasonalRateToApply=LOOKUP(1, 1/(($D4>=DateFrom)*($E4<=DateTo)),SeasonalIndicator))*("Y"=INDEX(WeekOpDays, 0, MATCH(H$3, WeekDays,0))),"---")

(entered in H4) with ctrl+shift enter

and then copy across and down