+ Reply to Thread
Results 1 to 10 of 10

Help with a nested, nested, nested formula

  1. 10-09-2017, 12:31 PM #1
    duanrd2
    duanrd2 is offline
    Registered User
    Join Date
    10-09-2017
    Location
    Philadelphia, PA
    MS-Off Ver
    Office 2013
    Posts
    1

    Question Help with a nested, nested, nested formula

    Hello. Below is a not too terribly complicated nested if statement:

    =IF(AND(D2>=55000,D2<=66000),IF(AND(C2="A"),K148,IF(AND(C2="B"),K149,IF(AND(C2="C"),K150,IF(AND(C2="D"),K151,IF(AND(C2="E"),K152,IF(AND(C2="F"),K153,0)))))))

    What I need to do now is to nest two other nearly identical formulas into this one, but can’t quite seem to get the syntax correct.

    The initial condition to be evaluated is the value of cell D2. So the second statement needs to account for a number that is >66000 and <=77000 while the third is for a number that is >77000 and <=88000. No numbers less than 55000 or greater than 88000 need to be evaluated.

    The other differences are the reference cells that the formula calls when the “text” conditions are true. So in the case where D2=70000, “A” will reference M148 and in the third, depending on the number in D2, an “A” will reference O148, and so on.

    Therefore, depending on the number in D2, the formula will travel down one of three possible logic paths to return a corresponding value contained in columns K, M, or O.
    Reply With QuoteRegister To Reply
  2. 10-09-2017, 12:49 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with a nested, nested, nested formula

    Hi and welcome to the forum,

    Usually when you find yourself extending IF functions beyond three or four consider if there might not be more efficient ways. Usually a VLOOKUP Table will do what you want.

    You'll probably need to concatenate your conditions in the first column of your lookup table. e.g.

    A66000
    B66000
    C66000
    .
    .
    A77000
    B77000
    C77000
    .
    .
    A88000
    B88000
    C88000

    and put the values of K148, K149,.....M148, O148...etc.

    If you can't put that together upload the workbook and manually add the results you expect, clearly explaining which are the results cells.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 10-09-2017, 01:30 PM #3
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help with a nested, nested, nested formula

    Cross-posted at https://www.mrexcel.com/forum/excel-...d-formula.html.

    You might want to take the time to read the rules on both forums.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  4. 10-09-2017, 01:42 PM #4
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help with a nested, nested, nested formula

    Try

    =INDEX(K148:O153,MATCH(C2,{"A","B","C","D","E","F"},0),(MATCH(D2,{55000,66001,77001})-1)*2+1)
    Reply With QuoteRegister To Reply
  5. 10-09-2017, 01:44 PM #5
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with a nested, nested, nested formula

    ....and noting shg's post this thread should receive no further responses until the cross posting link has been added
    Reply With QuoteRegister To Reply
  6. 10-09-2017, 01:45 PM #6
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with a nested, nested, nested formula

    Jonmo's post will be restored when the cross post link has been added
    Reply With QuoteRegister To Reply
  7. 10-09-2017, 04:00 PM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Help with a nested, nested, nested formula

    @Richard: has shg not already added the cross post link? As a new user, I’m guessing he's not familiar with (any) forum rules or moderation requirements.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  8. 10-09-2017, 04:09 PM #8
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with a nested, nested, nested formula

    Quote Originally Posted by TMS View Post
    @Richard: has shg not already added the cross post link? As a new user, I’m guessing he's not familiar with (any) forum rules or moderation requirements.
    Hello Trevor,

    Yes on reflection that was maybe a little harsh - to be honest it hadn't really registered with me that shg had added the link.
    I was looking for the OP to add it in the original.

    I'll Undelete the posts.

    An acknowledgment from the OP would be useful if only to demonstrate that he's read the rules.
    Reply With QuoteRegister To Reply
  9. 10-09-2017, 04:36 PM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,364

    Re: Help with a nested, nested, nested formula

    I was looking for the OP to add it in the original.
    Not unreasonable.


    An acknowledgment from the OP would be useful if only to demonstrate that he's read the rules.
    And that seems fair
    Reply With QuoteRegister To Reply
  10. 10-09-2017, 04:43 PM #10
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help with a nested, nested, nested formula

    An acknowledgment from the OP would be useful if only to demonstrate that he's read the rules.
    Not likely as there is resolution on the other thread.
    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] Nested vlookup or nested IFAND function?
    By jeptik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-11-2017, 02:27 AM
  2. Easier Way To Decipher Nested Nested IF's
    By Vladamir in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2015, 03:37 PM
  3. [SOLVED] Nested Sumif's or how to sum data based on nested criteria
    By dlietz in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-07-2014, 11:36 AM
  4. How to reduces Nested IF code(144 Nested IF) to achive same functionality
    By jobseeker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2014, 09:45 AM
  5. Share name > Physical Location > Security Groups > Nested Domain Groups > Nested Users
    By vioStyle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2012, 05:54 AM
  6. For each loop nested; getting stuck on one cell in first iteration of nested loop
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-22-2009, 11:54 AM
  7. help on a nested countif/nested if formula
    By vickiemc in forum Excel General
    Replies: 2
    Last Post: 08-13-2008, 08:29 AM

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