+ Reply to Thread
Results 1 to 14 of 14

Multi Variable Formula assistance needed

  1. #1
    Registered User
    Join Date
    02-23-2014
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Question Multi Variable Formula assistance needed

    Hello,
    I'm new to this forum so sorry for any faux pas.
    I'm trying to write a formula that calculates nightly rental rates of hotel rooms based on Unit, Season, and number of nights.

    Unit A in High Season for 1-6 nights has a rate of $145 per night
    Unit A in High Season for 7+ nights has a rate of $142.50 per night
    Unit A in Low Season for 1-6 nights has a rate of $130 per night
    Unit A in Low Season for 7+ nights has a rate of $120 per night
    Unit B in High Season for 1-6 nights has a rate of $320 per night
    Unit B in High Season for 7+ nights has a rate of $297 per night
    Unit B in Low Season for 1-6 nights has a rate of $288 per night
    Unit B in Low Season for 7+ nights has a rate of $274 per night
    Unit C in High Season for 1-6 nights has a rate of $400 per night
    Unit C in High Season for 7+ nights has a rate of $377 per night
    Unit C in Low Season for 1-6 nights has a rate of $360 per night
    Unit C in Low Season for 7+ nights has a rate of $354 per night

    I'd like to be able to pick the the Unit from a drop-down list, the season from a drop-down list, and then type in the number of nights and return the nightly rate and total rental for nights entered under those circumstances. Is this possible? I've experimented with IF and AND statements but can't seem to figure out how to tie in all variables. I know how to create the drop-down lists.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Multi Variable Formula assistance needed

    Take a look at this thread:

    http://www.excelforum.com/excel-char...onditions.html

    It seems to be a similar situation as you describe.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Multi Variable Formula assistance needed

    =SUMPRODUCT((A2:A13=A1)*(B2:B13=B1)*(C2:C13=C1)*(D2:D13))
    Without a xls file try a formula like this.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    02-23-2014
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multi Variable Formula assistance needed

    Here is the spreadsheet I started. I'm not sure where to put your formula.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multi Variable Formula assistance needed

    id probably do it something like this
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    02-23-2014
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multi Variable Formula assistance needed

    That will work! I could actually delete rows 3-13 and just use the drop-downs. Am I correct?

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Multi Variable Formula assistance needed

    or with the sumproduct formula

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multi Variable Formula assistance needed

    yep i just put them in there to show all variations

  9. #9
    Registered User
    Join Date
    02-23-2014
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multi Variable Formula assistance needed

    MartinDWilson,
    I like your version. But I'm having trouble altering the formula for 2 more units, or 8 more for that matter. Can you add Unit D and Unit E into the formula so I can see which part of the formula to adjust?
    Attached Files Attached Files

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multi Variable Formula assistance needed

    just make the ranges bigger here like this i increased them to 100
    =IFERROR(INDEX(IF(C2<7,$I$1:$I$100,$J$1:$J$100),MATCH(A2&B2,INDEX($G$1:$G$100&$H$1:$H$100,0),0)),"")

  11. #11
    Registered User
    Join Date
    02-23-2014
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multi Variable Formula assistance needed

    Thanks martindwilson!

  12. #12
    Registered User
    Join Date
    02-23-2014
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multi Variable Formula assistance needed

    RATE Calculator.xlsxHi Martin,

    I need to add a 3rd time frame to my formula. Right now, the variables are "low" or "high" season and "less than 7 days" or "7 + days". I need to change "7 + days" to "7 - 21 days" and add another timeframe: "22 + days" which will have a lower rate than "7 - 21 days". Here is the current formula: =IFERROR(INDEX(IF(D3<7,$D$5:$D$18,$E$5:$E$18),MATCH(B3&C3,INDEX($B$5:$B$18&$C$5:$C$18,0),0)),"")

    Can you help me add the new timeframe? I've attached my spreadsheet.
    Alex

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multi Variable Formula assistance needed

    well you certainly broke that so here it is fixed with new rate added
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-23-2014
    Location
    Ventura, CA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multi Variable Formula assistance needed

    Thank you again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. additional help needed on multi retrieve formula
    By deancorleone in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2013, 11:22 AM
  2. [SOLVED] Logical Formula Assistance Needed
    By respuzy in forum Excel General
    Replies: 6
    Last Post: 03-24-2012, 09:44 AM
  3. Assistance needed to apply a formula
    By banny85 in forum Excel General
    Replies: 2
    Last Post: 06-23-2011, 06:33 AM
  4. Formula Assistance Needed
    By MoonShot in forum Excel General
    Replies: 4
    Last Post: 01-29-2010, 03:02 AM
  5. [SOLVED] Multi Variable Formula
    By MadCap via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-24-2005, 04:06 PM

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