+ Reply to Thread
Results 1 to 8 of 8

Multiple Criteria formula - HELP DESPERATELY NEEDED!

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    London
    MS-Off Ver
    Excel 2011
    Posts
    3

    Multiple Criteria formula - HELP DESPERATELY NEEDED!

    Hello,

    Hope you are all well. I've been racking my brains on how to solve this problem and I sadly my brain simply isn't up to the challenge. I run a small travel company and I want to set up a "hotel price calculator" on excel. On the back tabs I have a list of months (may, june, july etc), a list of hotels and a list of room grade (standard, suite etc) and finally a price for every single potential outcome.

    Ultimately I want to be able to select a Month, followed by a hotel, followed by room grade and then for the price recorded on the back tabs for that criteria, to automatically appear.

    Any help would be hugely appreciated.

    Many thanks,

    James

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Multiple Criteria formula - HELP DESPERATELY NEEDED!

    Hello James and welcome to the Forum....could you upload a data set with all of the company confidential stuff removed....thanks
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    05-17-2013
    Location
    London
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Multiple Criteria formula - HELP DESPERATELY NEEDED!

    Front Sheet.jpgBack Sheet.jpg

    As you'll see from the back sheet I was playing around with having a with how I presented the back sheet (see May vs June), happy to be flexible to what the relevant formula requires.

    Let me know your thoughts.

  4. #4
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Multiple Criteria formula - HELP DESPERATELY NEEDED!

    Thanks James.....I was sort of hoping you could upload the excel file so I don't have to type a bunch of stuff in, in the process of creating the formulas....thanks

  5. #5
    Registered User
    Join Date
    05-17-2013
    Location
    London
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Multiple Criteria formula - HELP DESPERATELY NEEDED!

    Sorry, I figured as much but I'm new to this and I couldn't find the icon that would let me attached files - only images! You help is much appreciated.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple Criteria formula - HELP DESPERATELY NEEDED!

    See if you can adapt this to your application.

    Let's assume this is your data in the range A2:D5...

    A...B...C...10
    D...E...F...14
    G...H...I...11
    J...K...L...32

    You want to return the price for the combination of D, E and F.

    Use cells to hold the criteria:

    A10 = D
    B10 = E
    C10 = F

    Enter this array formula** in D10:

    =INDEX(D2:D5,MATCH(1,IF(A2:A5=A10,IF(B2:B5=B10,IF(C2:C5=C10,1))),0))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    DaveDeV
    Guest

    Re: Multiple Criteria formula - HELP DESPERATELY NEEDED!

    Hi James,

    This application would be better handled in a database application than a spreadsheet for one major reason - you need to be able to book more than 12 months ahead so your "Month" will also need to include a "Year" component to allow Excel to differentiate between this May in 2013 and next May in 2014 when the rates may well be different.

    Also, your "Hotel Locator" needs to also allow for "Country" and "City/Town/Suburb" parameters to handle hotel chains with more than one hotel per country and city.

    The number of potential hotels (including permutations of Chain, Country, City, Suburb) will make selection via drop-down selection less than attractive.

    The only solution I can come up with is to construct a table with a "key" comprising a concatenation of all permutation identifiers including room grade and populate additional 12 columns with the rates for the 12 months. Using drop-downs, select each component of the key and use the month number to handle the LOOKUP offset to get the right rate for the month... This not ideal as the key construction could result in some complexity in the sub-tables to avoid constructing an invalid permutation (eg Southern Sun Hotel / Patagonia / Helsinki / Hyde Park)

    Rather you than me...

    Dave
    Last edited by DaveDeV; 05-17-2013 at 12:05 PM.

  8. #8
    DaveDeV
    Guest

    Re: Multiple Criteria formula - HELP DESPERATELY NEEDED!

    The sub-tables mentioned allude to the need for a set of hierarchical tables that allow for "structures" whereby:

    a) A Country Table would have a sub-table containing all potential locations in that country and nowhere else
    b) The location sub-table would have a lower level of sub-table containing all potential hotels in that location
    c) A hotel sub-sub-table would need a further level of sub-table for room grades available and rates by month by year

    This implies a data taxonomy that would resemble one of those hen & chickens plants...

    Complex to build and a pain in the rear to maintain

    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1