+ Reply to Thread
Results 1 to 4 of 4

Combine two options

Hybrid View

  1. #1
    Registered User
    Join Date
    02-13-2019
    Location
    canada
    MS-Off Ver
    2010
    Posts
    56

    Combine two options

    Ok, I think I can eventually figure this out but I have been looking at it too long today.

    Basically if cell $B4 is Bew TRUE then 1, IF cell $B4 FALSE then 2. I have the formula working independently, now I am just stuck on god forsaken brackets. I am also aware there may be better ways to do this whole thing in general, but when imagination fails brute force works.

    Based on the input of a cell "if(Data Input! $B4 = "Bew" " I want then to have these two options in-cased in the initial if statement:

    1:
    IF($B65<=$J$79,IF(AND(C65<$C$79,C65>$F$79),C65,IF(ROW()-ROW($C$86)=1,0,"")),
    IF($B65<=$D$80,IF(AND(C65<((($C$80-$C$79)/($D$80-$J$79))*($B65-$J$79)+$C$79),C65>((($F$80-$F$79)/($D$80-$J$79))*($B65-$J$79)+$F$79)),C65,""),
    IF($B65<=$D$81,IF(AND(C65<((($C$81-$C$80)/($D$81-$D$80))*($B65-$D$80)+$C$80),C65>((($F$81-$F$80)/($D$81-$D$80))*($B65-$D$80)+$F$80)),C65,""),
    IF($B65<=$D$82,IF(AND(C65<((($C$82-$C$81)/($D$82-$D$81))*($B65-$D$81)+$C$81),C65>((($F$82-$F$81)/($D$82-$D$81))*($B65-$D$81)+$F$81)),C65,""),
    IF($B65<=$K$79,IF(AND(C65<$J$79,C65>$M$79),C65,IF(ROW()-ROW($C$86)=1,0,""))))),
    2:
    IF($B65<=$K$79,IF(AND(C65<$J$79,C65>$M$79),C65,IF(ROW()-ROW($C$86)=1,0,""))))),
    IF($B65<=$K$80,IF(AND(C65<((($J$80-$J$79)/($K$80-$K$79))*($B65-$K$79)+$J$79),C65>((($M$80-$M$79)/($K$80-$K$79))*($B65-$K$79)+$M$79)),C65,""),
    IF($B65<=$K$81,IF(AND(C65<((($J$81-$J$80)/($K$81-$K$80))*($B65-$K$80)+$J$80),C65>((($M$81-$M$80)/($K$81-$K$80))*($B65-$K$80)+$M$80)),C65,""),
    IF($B65<=$K$82,IF(AND(C65<((($J$82-$J$81)/($K$82-$K$81))*($B65-$K$81)+$J$81),C65>((($M$82-$M$81)/($K$82-$K$81))*($B65-$K$81)+$M$81)),C65,""))))))

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,645

    Re: Combine two options

    Basical,
    =if(Data Input! $B4 = "Bew",formula 1, formula 2)
    Anyway, both 1 & 2 seems possibly be shorter, if you can upload sample worksheet, to see if we can make it better.
    Quang PT

  3. #3
    Registered User
    Join Date
    02-13-2019
    Location
    canada
    MS-Off Ver
    2010
    Posts
    56

    Re: Combine two options

    Ok here is a page with the offending formulas.The reason I went with the complicated formula is because it is a complicated sheet that requires a lot of step by step nonsense. I think I have successfully uploaded the page.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,864

    Re: Combine two options

    Try the following:
    Formula: copy to clipboard
    =CHOOSE(IF(B4="bew",1,2),IF($B65<=$J$79,IF(AND(C65<$C$79,C65>$F$79),C65,IF(ROW()-ROW($C$86)=1,0,"")),
    IF($B65<=$D$80,IF(AND(C65<((($C$80-$C$79)/($D$80-$J$79))*($B65-$J$79)+$C$79),C65>((($F$80-$F$79)/($D$80-$J$79))*($B65-$J$79)+$F$79)),C65,""),
    IF($B65<=$D$81,IF(AND(C65<((($C$81-$C$80)/($D$81-$D$80))*($B65-$D$80)+$C$80),C65>((($F$81-$F$80)/($D$81-$D$80))*($B65-$D$80)+$F$80)),C65,""),
    IF($B65<=$D$82,IF(AND(C65<((($C$82-$C$81)/($D$82-$D$81))*($B65-$D$81)+$C$81),C65>((($F$82-$F$81)/($D$82-$D$81))*($B65-$D$81)+$F$81)),C65,""),
    IF($B65<=$K$79,IF(AND(C65<$J$79,C65>$M$79),C65,IF(ROW()-ROW($C$86)=1,0,""))))))),IF($B65<=$K$79,IF(AND(C65<$J$79,C65>$M$79),C65,IF(ROW()-ROW($C$86)=1,0,"")),
    IF($B65<=$K$80,IF(AND(C65<((($J$80-$J$79)/($K$80-$K$79))*($B65-$K$79)+$J$79),C65>((($M$80-$M$79)/($K$80-$K$79))*($B65-$K$79)+$M$79)),C65,""),
    IF($B65<=$K$81,IF(AND(C65<((($J$81-$J$80)/($K$81-$K$80))*($B65-$K$80)+$J$80),C65>((($M$81-$M$80)/($K$81-$K$80))*($B65-$K$80)+$M$80)),C65,""),
    IF($B65<=$K$82,IF(AND(C65<((($J$82-$J$81)/($K$82-$K$81))*($B65-$K$81)+$J$81),C65>((($M$82-$M$81)/($K$82-$K$81))*($B65-$K$81)+$M$81)),C65,""))))))

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Combine VLOOKUP with OR Function with three options
    By kibbles in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-22-2020, 08:00 AM
  2. Combine Options for Single Argument
    By Dimonium_Anonimo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-14-2018, 09:34 PM
  3. [SOLVED] Can't find corrext Index Match formula where different options have same sub-options
    By Ochimus in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-27-2016, 06:59 AM
  4. Select and combine 5 different items together maximizing 5 different options
    By holyexcel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-07-2015, 02:27 AM
  5. Replies: 0
    Last Post: 08-26-2014, 08:44 AM
  6. extracting text string with 2 options @ end & limited options
    By ChristianR in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-24-2010, 06:51 AM
  7. [SOLVED] Working with options from within Tools Options clears the Clipboar
    By Peter Rooney in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-18-2005, 12:55 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