+ Reply to Thread
Results 1 to 3 of 3

IFS function. Many IFS and multiple possible outcomes

  1. #1
    Registered User
    Join Date
    11-13-2014
    Location
    Aalborg, Denmark
    MS-Off Ver
    Excell 2013
    Posts
    1

    IFS function. Many IFS and multiple possible outcomes

    I’m trying to create one function capable of testing my data by the following criteria:

    If x<=70, then 1
    If 70<x<77.5, then 2
    If 77.5<=x<82.5, then 3
    If 82.5<=x<87.5, then 4
    If 87.5<=x<95, then 5
    If 95<x<=100, then 6
    If 100<x<=110, then 7
    If 110<x<=130, then 8

    I hope you can help me with this problem.

    Thanks in advance

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IFS function. Many IFS and multiple possible outcomes

    Try

    =MATCH(x,{0,70,77.5,82.5,87.5,95,100,110,130})


    FYI, 130 or more will be 9, and any values Less than 0 will return #N/A

  3. #3
    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,917

    Re: IFS function. Many IFS and multiple possible outcomes

    or for a bit more flexibility, use a small table with a vlookup...

    A
    B
    C
    1
    150
    0
    1
    2
    8
    70
    2
    3
    77.5
    3
    4
    82.5
    4
    5
    87.5
    5
    6
    95
    6
    7
    100
    7
    8
    130
    8

    A2=VLOOKUP(A1,$B$1:$C$8,2,1)
    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 to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF function with multiple Trues, multiple outcomes
    By BiminiSharklab in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2014, 10:50 AM
  2. [SOLVED] An IF function with 4 outcomes
    By Lija in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-05-2013, 04:34 AM
  3. IF Function multiple possible outcomes?
    By Nomadski in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 02-11-2013, 05:41 PM
  4. [SOLVED] If function with 3 outcomes. As well as 2nd IF function to factor positive or negitive
    By Danimal615 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2013, 02:36 PM
  5. [SOLVED] If Function with multiple outcomes and accumulating numbers
    By Brainstorm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2012, 07:04 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