+ Reply to Thread
Results 1 to 8 of 8

IF/AND with six possible results...help with formula

  1. #1
    Registered User
    Join Date
    01-02-2018
    Location
    Stockton, CA
    MS-Off Ver
    2013
    Posts
    3

    IF/AND with six possible results...help with formula

    Hello,

    I was wondering If I could get help with a formula for the following table. I'm a psychologist tracking patient change, looking for a way to make data more user friendly. As you can see, I have one column with three change specifiers, an adjacent column with two severity specifiers, and then a column with six possible outputs. Is there a way to work the IF/AND functions (or another function) to enable the six results? The last error I got was that there were "too many arguments." Thanks in advance!

    Conditions to Be Used for Specifier
    X=Reliably Improved AND Y=Clinical Range On Track
    X=Reliably Improved AND Y=subclinical Recovery
    X=No Change AND Y=Clinical Range Not on Track
    X=No Change AND Y=subclinical Consider Termination
    X=Reliably Worse AND Y=Clinical Range At Risk
    X=Reliably Worse AND Y=subclinical Psychoeducation

  2. #2
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - Office 365
    Posts
    8,098

    Re: IF/AND with six possible results...help with formula

    Hi seancamw. Welcome to the forum.

    Is there a way to work the IF/AND functions (or another function) to enable the six results?
    Please clarify. "Enabling the six results" is vague.

    Perhaps if you also post an example of desired outputs it would help.

    Better yet please attach an anonymized Excel workbook ... not pics or screenshots; saves having to retype data. You will often get faster and more response doing that.
    Be sure to include a BEFORE section and an AFTER section.

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,524

    Re: IF/AND with six possible results...help with formula

    Hi all. If I'm following you, you want to use the two types of specifiers as inputs, and return the corresponding output...in which case a table like the one shown below (in BLUE) might make more sense:

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Case # Change Severity Output (FORMULA) LOOKUP VALUES Clinical Subclinical
    2
    psy0001 No Change Clinical Clinical Range Not on Track Reliably Improved Clinical Range On Track subclinical Recovery
    3
    psy0002 Reliably Worse Subclinical subclinical Psychoeducation No Change Clinical Range Not on Track subclinical Consider Termination
    4
    psy0003 Reliably Improved Clinical Clinical Range On Track Reliably Worse Clinical Range At Risk subclinical Psychoeducation
    5
    psy0004 Reliably Worse Clinical Clinical Range At Risk
    6
    psy0005 Reliably Improved Subclinical subclinical Recovery


    This lets you use a simple lookup formula (column D in the example) to find the outputs:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by leelnich; 01-02-2018 at 11:45 PM.
    Clicking the Add Reputation star below any helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion. (Thread Tools above Post # 1) Thanks!-Lee

  4. #4
    Registered User
    Join Date
    01-02-2018
    Location
    Stockton, CA
    MS-Off Ver
    2013
    Posts
    3

    Re: IF/AND with six possible results...help with formula

    Thank you for the prompt feedback! See attached. I looked at the INDEX/MATCH formula, and it does produce my desired results in a table, but I'm not sure how to use that formula to apply to my dataset.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - Office 365
    Posts
    8,098

    Re: IF/AND with six possible results...help with formula

    I'm confused. leelnich's formula does exactly that.

    Below as applied to the latest upload.

    What am I missing?

    Lee's formula in G2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Produces this.


    A
    B
    C
    D
    E
    F
    G
    1
    Case #
    Session #
    Outcome_ans
    Status
    Range
    Specifier
    2
    psy001
    1
    23
    Clinical Range
    #N/A
    3
    psy001
    2
    12
    -11
    Reliably improved
    Clinical Range
    On Track
    4
    psy001
    3
    9
    -3
    No change
    Subclinical
    Consider Termination
    5
    psy001
    4
    12
    3
    No change
    Clinical Range
    Not on Track
    6
    psy001
    5
    12
    0
    No change
    Clinical Range
    Not on Track
    7
    psy001
    6
    12
    0
    No change
    Clinical Range
    Not on Track
    8
    psy001
    7
    25
    13
    Reliably Worse
    Clinical Range
    At Risk
    9
    psy001
    8
    21
    -4
    No change
    Clinical Range
    Not on Track
    10
    psy001
    9
    22
    1
    No change
    Clinical Range
    Not on Track
    11
    psy001
    10
    18
    -4
    No change
    Clinical Range
    Not on Track
    12
    psy001
    11
    15
    -3
    No change
    Clinical Range
    Not on Track
    13
    psy001
    12
    11
    -4
    No change
    Clinical Range
    Not on Track
    14
    psy001
    13
    8
    -3
    No change
    Subclinical
    Consider Termination
    15
    psy001
    14
    5
    -3
    No change
    Subclinical
    Consider Termination
    16
    psy001
    15
    11
    6
    Reliably Worse
    Clinical Range
    At Risk
    17
    psy001
    16
    2
    -9
    Reliably improved
    Subclinical
    Recovery
    18
    psy001
    17
    9
    7
    Reliably Worse
    Subclinical
    Psychoeducation
    19
    20
    21
    22
    LOOKUP VALUES
    Clinical Range
    Subclinical
    23
    Reliably Improved
    On Track
    Recovery
    24
    No Change
    Not on Track
    Consider Termination
    25
    Reliably Worse
    At Risk
    Psychoeducation

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,524

    Re: IF/AND with six possible results...help with formula

    The attached file offers three different solutions:
    1) INDEX using your specifications on Sheet1
    2) INDEX using my table on Sheet2
    3) This rather unwieldy NESTED IFS formula:
    Please Login or Register  to view this content.
    Take your pick!
    Attached Files Attached Files
    Last edited by leelnich; 01-03-2018 at 03:11 PM.

  7. #7
    Registered User
    Join Date
    01-02-2018
    Location
    Stockton, CA
    MS-Off Ver
    2013
    Posts
    3

    Re: IF/AND with six possible results...help with formula

    Thank you so much Lee! I think what I needed as a relative novice with Excel was a little spelling out of things, and this last attachment does just that. Very much appreciated!

  8. #8
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,524

    Re: IF/AND with six possible results...help with formula

    You're most welcome! If that concludes your thread, please go to Thread Tools (up top) and mark it as SOLVED. Regards - Lee
    ...and since you're new: Clicking the Add Reputation star at the bottom of helpful posts is always a nice way to show your appreciation to everyone who helped.

+ 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