Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Building a formula involving dates and costs

Latest post Sat, Apr 18 2015 11:05 AM by Chele. 5 replies.
  • Tue, Dec 3 2013 8:44 PM

    • RozDel
    • Not Ranked
    • Joined on Wed, Dec 4 2013
    • Posts 2
    • Points 26

    Building a formula involving dates and costs

    I have a question about building an excel formula involving dates and costs. I am dealing with a rooming list for hotel guests and room costs based on dates. I'm working with a single spreadsheet which MUST have filterable columns for: 

    guest name (about 250 people) 

    hotel name (there are four different hotels) 

    date in and date out (they must be separate columns as I often need to sort by ascending or descending) 

    total cost per room

    I'd gladly add in any other columns to allow me to build the formula necessary but the general layout must be like the grid linked here: https://dl.dropboxusercontent.com/u/5864034/Test_Excel.xls

    What's causing me a problem is: each hotel has different rates and these rates vary according to the date! The one variable that remains the same is that all four hotels increase/decrease their prices on the same dates. Here's an example of one hotel's costs: the room price from Jan 1- Jan 25 = $129. From Jan 26-Jan 30= $159. From Jan 31- Feb 2 = $282. From Feb 3-Feb 8= $159. Is there any way to create a formula that considers the date as a factor in calculating the total cost per room? 

     

    I was thinking of adding four additional columns, one for each price range (one: Jan 1- Jan 25; two: Jan 26- Jan 30; three Jan 31- Feb 2; four: Feb 3- Feb 8) so that I can use the IN and OUT date for each guest and tally up how many nights per price range and then multiply it by the rate and then add up those sums in a final column with the grand total for the room for that person. I imagine the formula would need to refer to the IN and OUT date to count how many days are in each price tier to then multiply by the room cost, but I can't even figure out how to do this! Any help would be greatly appreciated! I've uploaded a sample of the spreadsheet layout I need to use in dropbox: https://dl.dropboxusercontent.com/u/5864034/Test_Excel.xls

    • Post Points: 21
  • Wed, Dec 4 2013 5:34 PM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 235
    • Points 3,551

    Re: Building a formula involving dates and costs

    A way of approaching these types of problem is to make use of Excel array formulas.

    This method is relatively easy to implement but you will need to read about array formulas if you want to understand how it works.

    Two ranges have been given fairly obvious names and you will probably need to adjust these when you use a full data set
    rngDate           =Sheet1!$J$2:$J$5

    and
    rngHotel           =Sheet1!$K$1:$L$1

    and four other named formulas have been defined. You can view them all using the Name manager, I've put a list of them on the Names tab.

    The named formula arrDays makes an array of with the an element referring to the cost row for each day that a person stays. This formula assumes that the date-in and date-out columns are C and D respectively and that your lookup table is ordered by increasing dates.

    Two other ranges are defined

    rngPrice          =OFFSET(rngDate,0,nHotel)
    is the list of prices for the hotel defined in column B

    rngRow           =OFFSET(rngDate,0,-1)
    is the set of integers to the left of the dates lookup table.

    The cost formula which must be array entered (i.e. Ctrl-Shift-Enter) is
    =SUM(LOOKUP(arrDays,rngRow,rngPrice))

    For each of the elements in arrDays, it finds the position in the rngRow range and returns the equivalent price from the rngPrice range. Because it is array entered, SUM adds up all the lookup values to give a total cost.

    Peter

    • Post Points: 37
  • Wed, Dec 4 2013 5:56 PM In reply to

    • RozDel
    • Not Ranked
    • Joined on Wed, Dec 4 2013
    • Posts 2
    • Points 26

    Re: Building a formula involving dates and costs

    Wow, that's great! Thank you so much for that. 

    • Post Points: 5
  • Fri, Apr 17 2015 10:29 PM In reply to

    • Chele
    • Not Ranked
    • Joined on Sat, Apr 18 2015
    • Posts 2
    • Points 26

    Re: Building a formula involving dates and costs

    This is extremely helpful and I have followed it to come very close to the correct setup of my own worksheet, however I am still missing the final answer.  The formula 'sees' the correct number of days, but is unable to associate $ values with it. Would anyone have a moment to look at this and tell me what I've done incorrectly?

    https://www.dropbox.com/s/6z9oly7339udopq/Aliante%20Test.xlsx?dl=0

    Thank you.

    • Post Points: 21
  • Sat, Apr 18 2015 3:05 AM In reply to

    • PeterG
    • Top 10 Contributor
    • Joined on Mon, Feb 25 2008
    • Posts 235
    • Points 3,551

    Re: Building a formula involving dates and costs

    So nearly there. Just two small changes needed.

    • The name ALIANTE!rngRow =OFFSET(ALIANTE!rngDate,0,0) should be =OFFSET(ALIANTE!rngDate,0,-1)
    • Column I needs the numbers 1, 2, 3, ... entering in it starting ar row 2 and going to the bottom of your data range
    One last change that I would suggest is to add another name such as TotalCost = SUM(LOOKUP(arrDays,rngRow,rngPrice)). All you need to do is to add =TotalCost in column F rather than having to enter the array formula in each cell. (Note, Excel treats name formulas as array formulas by default).
    Peter

    • Post Points: 21
  • Sat, Apr 18 2015 11:05 AM In reply to

    • Chele
    • Not Ranked
    • Joined on Sat, Apr 18 2015
    • Posts 2
    • Points 26

    Re: Building a formula involving dates and costs

    Sir, you are a rock star!  Thank you so much.  Arrays, Ranges & Offset are all new to me and I really appreciate your help in learning to use them.

    • Post Points: 5
Page 1 of 1 (6 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.