Excel 2008 : Help with adding results derived from VLOOKUP

**SunshineCharlie** · 04-25-2012, 01:11 PM

Hi,
I'm new to the forum and tried searching for a post with the info I'm looking for, couldn't find the answer. So I'm posting in hope of someone being able to help!

My worksheet is a Rental Rate Calculator, used to determine the amount of rental rate based on a few variables:
1) type of rental condo
2) number of rental nights the guests will stay
3) nightly rental rate based on the rental season

Based on the user selecting the condo type, arrival date and number of nights they would like to stay, I am trying to make this worksheet deliver the total rental rate based on the provided criteria.

I have used VLOOKUP to determine the nightly rate based on the user inputted arrival date and the condo type selected
I have used VLOOKUP to determine the last night of stay (based on the number of nights input by the user)
Once I have the last night of stay, I can use VLOOKUP to determine to nightly rate of the last night's stay.

THE PROBLEM: How do I create a formula to add up all of the nightly rates in the range of dates? This formula would need to reference the results of two VLOOKUP formulas.

Sorry if this doesn't make sense...my head is spinning over this!

I have attached the excel file.

BTW - I'm using Excel 2008 for Mac.

Please help!
CharlieRental Rate Calculator 2.xls

**Paul** · 04-25-2012, 02:16 PM

Hi Charlie,

For cell C15 (Last night of Stay) you should probably use something like:

=IF(AND(A18="",A22=""),"",IF(A18="",A22-1,A15+A18-1))

Cell C22 would then no longer be necessary, as the last night of stay would be calculated whether you entered the number of nights in A18 OR the departure date in A22. You could then calculate the total rate for the stay (sum of nightly rates combined) in D17 using:

=SUMIFS(M:M,L:L,">="&A15,L:L,"<="&C15)

or

=SUMPRODUCT((L4:L2500>=A15)*(L4:L2500<=C15)*(M4:M2500))

**SunshineCharlie** · 04-25-2012, 03:14 PM

Thanks very much Paul, it's working now!

It seems your suggestion for cell C15 returns a numeric value that I don't understand. Is there a way to have it display the calendar date?

There is one more piece to the puzzle for me, that I haven't determined yet. Perhaps you could shed some light on it for me?
When a guest is booking with us for more than 15 nights we have a discount structure.
When the stay is longer than 30 nights there is a higher discount applied
For more than 90 nights there is a higher discount.

We're still playing with the discount percentages, so I'm wondering if there is an easy way to work this factor into the calculation?

Charlie

**Paul** · 04-25-2012, 03:31 PM

Hi Charlie,

The "numeric value" is actually a serial representation of the date (it's how Excel actually stores the date value - as the number of days since 1/1/1900). Format the cell as Date and you should be set.

As for your discounts, you could probably use something like:

=SUMIFS(M:M,L:L,">="&A15,L:L,"<="&C15)*LOOKUP(C15-A15+1,{0,15,30,90},{1,0.9,0.8,0.7})

The first part of the formula is the same - which calculates the total stay amount. The LOOKUP function then determines the number of nights and compares it against 0, 15, 30 and 90. Consider those as buckets, with each number starting a new bucket. (0-14, 15-29, 30-89, 90+). Based on which bucket the # of nights falls into, the corresponding multiplier in the second array is returned. (1, .9, .8 or .7 to represent 100%, 90%, 80% and 70%). 90% represents a 10% discount, of course; 80% is a 20% discount, etc. You can adjust according to your structure. You can add buckets as well, just remember to add them in ascending order to the first array, and insert the discount amount in that same position in the second array.

**SunshineCharlie** · 04-25-2012, 04:05 PM

I've run into another formula problem. I'm trying to refine the calculator by showing the adjusted nightly rate based on the number of nights and associated discount (if any).

I tried using this formula but it didn't work: =(D17/A18)*OR(D17/(A22-A15))

It's easy enough to calculate the adjusted night rate if the user inputs the number of nights the guests will stay, but calculating the nightly rate gets harder if that field is empty when the user inputs the departure date instead. Is there a quick fix?

Rental Rate Calculator 3.xls

**SunshineCharlie** · 04-25-2012, 03:50 PM

Thank you Paul, you're a life saver!!

**Paul** · 04-25-2012, 04:16 PM

By "adjusted nightly rate" I assume you mean "average nightly rate"? If so, try:

=IF(AND(A18="",A22=""),"",IF(A18="",D17/(A22-A15),D17/A18))

Excel 2008 : Help with adding results derived from VLOOKUP

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Help with adding results derived from VLOOKUP

Re: Help with adding results derived from VLOOKUP

Re: Help with adding results derived from VLOOKUP

Re: Help with adding results derived from VLOOKUP

Re: Help with adding results derived from VLOOKUP

Re: Help with adding results derived from VLOOKUP

Re: Help with adding results derived from VLOOKUP

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions