+ Reply to Thread
Results 1 to 8 of 8

IF Formula for Finding numbers in a range?

  1. #1
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    IF Formula for Finding numbers in a range?

    Hi folks,

    I have a larger range of Sales Probabilities, and i want to group them in categories, for example:
    - 0-25 = Step 1
    - 25-50 = Step 2
    - 50 - 75 = Step 3
    - 75+ = Step 4.

    I'm sure its simple, so sorry if this is a menial ask.

    Thanks again.
    Rohan F

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: IF Formula for Finding numbers in a range?

    if A1 is the cell your looking at
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    alternative formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by humdingaling; 06-23-2015 at 11:51 PM.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: IF Formula for Finding numbers in a range?

    F
    G
    1
    0
    Step1
    2
    25
    Step2
    3
    50
    Step3
    4
    75
    Step4


    A
    B
    1
    Data Formula Result
    2
    55
    =VLOOKUP(A2,$F$1:$G$4,2,TRUE)
    3
    3
    =VLOOKUP(A3,$F$1:$G$4,2,TRUE)
    4
    27
    =VLOOKUP(A4,$F$1:$G$4,2,TRUE)
    5
    88
    =VLOOKUP(A5,$F$1:$G$4,2,TRUE)
    6
    200
    =VLOOKUP(A6,$F$1:$G$4,2,TRUE)
    7
    1
    =VLOOKUP(A7,$F$1:$G$4,2,TRUE)


    A
    B
    1
    Data Formula Result
    2
    55
    Step3
    3
    3
    Step1
    4
    27
    Step2
    5
    88
    Step4
    6
    200
    Step4
    7
    1
    Step1


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: IF Formula for Finding numbers in a range?

    Thanks again folks for prompt responses, great forum we have here!

  5. #5
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: IF Formula for Finding numbers in a range?

    In regards to IF finding functions, is it possible to a between search, ie great than XX but less or equal XX ?

    That would be handy to fine tune number ranges.

    I tried doing it and got formula errors, so figuring i needed added layer of hypothesis.

    Anyway thanks again for all the support.
    regards
    Rohan

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: IF Formula for Finding numbers in a range?

    the IF is nested/layered already

    if a1<26 = -negative whatever to 26
    if a1<51 = must be above 26 but less than 51
    if a1<76 = must be above 51 but lower than 76
    A1 is none of these = must be above 76

    if you want to tune the number ranges just change the numbers

    put all 3 methods into one spreadsheet for you
    Attached Files Attached Files
    Last edited by humdingaling; 06-24-2015 at 12:12 AM.

  7. #7
    Registered User
    Join Date
    04-16-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    26

    Re: IF Formula for Finding numbers in a range?

    Okay cool I get, the IF function finds values alternatively in each layer.

    I thought maybe it would bring all values underneath for high levels of probability, but the nesting affect, suits me perfectly.
    Thanks heaps

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: IF Formula for Finding numbers in a range?

    of course you can do it the other way starting from your max instead and using greater than

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Finding TWO numbers from a range of numbers to match a target value
    By Eero in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2014, 10:08 AM
  2. Formula for finding a specific sum from a range of numbers?
    By jdawg1536 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-18-2014, 04:19 AM
  3. [SOLVED] Formula for finding range of numbers excluding outliers
    By Granny Nanny in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2012, 10:33 AM
  4. Replies: 0
    Last Post: 01-29-2012, 10:05 PM
  5. [SOLVED] finding range of numbers in one row to another row
    By Mike in forum Excel General
    Replies: 5
    Last Post: 01-19-2006, 07:30 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