+ Reply to Thread
Results 1 to 7 of 7

Simpler way than 10+ nested IF statements?

  1. 09-14-2015, 06:58 PM #1
    jobinv123
    jobinv123 is offline
    Registered User
    Join Date
    09-14-2015
    Location
    texas
    MS-Off Ver
    2010
    Posts
    2

    Question Simpler way than 10+ nested IF statements?

    I've been stuck on this for hours, any excel wizards know of a way to simplify this problem?

    I have a spreadsheet that calculates the gas rate based on what area it is located in (there are 24 areas). Instead of making multiple nested if statements with vlookups embedded, is there any easier way to do this? Perhaps VBA or an array function?

    See attached excel spreadsheet. Gas rate is calculated in column E3.

    Forecasting Master Excel Help.xlsx

    Thank you
    Reply With QuoteRegister To Reply
  2. 09-14-2015, 07:52 PM #2
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Simpler way than 10+ nested IF statements?

    There is


    Kind regards
    Leo
    Reply With QuoteRegister To Reply
  3. 09-14-2015, 07:53 PM #3
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Simpler way than 10+ nested IF statements?

    en the file


    little error
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 09-14-2015, 08:37 PM #4
    ncmay
    ncmay is offline
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Simpler way than 10+ nested IF statements?

    Try the attached file on sheet2.
    I changed the lookup area and the formula.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 09-14-2015, 10:54 PM #5
    jobinv123
    jobinv123 is offline
    Registered User
    Join Date
    09-14-2015
    Location
    texas
    MS-Off Ver
    2010
    Posts
    2

    Re: Simpler way than 10+ nested IF statements?

    Quote Originally Posted by ncmay View Post
    Try the attached file on sheet2.
    I changed the lookup area and the formula.
    Wow this is great thank you! What would you do if the lookup value columns are all irregular in size? See tab 1 in this file attached below.

    Forecasting Master Excel Help2.xlsx
    Reply With QuoteRegister To Reply
  6. 09-14-2015, 11:12 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Simpler way than 10+ nested IF statements?

    LeoTaxi and ncmay, Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  7. 09-14-2015, 11:40 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Simpler way than 10+ nested IF statements?

    jobinv, try this, cvopied down...
    =INDEX($H$3:$AA$14,MATCH($D2,OFFSET($H$1,2,MATCH("Area "&$A2,$H$1:$AA$1,0)-1,50,1)),MATCH("Area "&$A2,$H$1:$AA$1,0)+1)
    (I think your last answer - 5900 - is wrong. That value is against 0.62, not 0.75)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Simpler way than 11+ If Statements?
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-28-2015, 04:24 PM
  2. Scheduler Tool works but can it be simpler? Does anyone have a simpler scheduling formula?
    By Martin Chamberlin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-29-2013, 04:50 AM
  3. If(AND) Nested Function long and complicated. Looking for simpler way if there is
    By cocostar88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-22-2013, 05:39 PM
  4. Help make this simpler (if statements)
    By harmbasi in forum Excel General
    Replies: 3
    Last Post: 04-13-2012, 02:58 PM
  5. A simpler way to do nested vlookups?
    By bsoper in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 03:43 PM
  6. Looking for a simpler formula instead of using may nested "IF"s
    By Ogbuehi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-19-2009, 11:15 AM
  7. Nested IF statements or CHOOSE statements needed for multiple conditions formula
    By salmansaif in forum Excel General
    Replies: 12
    Last Post: 05-15-2009, 08:38 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1