+ Reply to Thread
Results 1 to 6 of 6

Output a specific number from range of numbers in one cell

  1. #1
    Registered User
    Join Date
    09-17-2014
    Location
    Trinidad
    MS-Off Ver
    Ms Office 2010
    Posts
    3

    Output a specific number from range of numbers in one cell

    Hello,

    I'am looking for a formula that can take a range of numbers from one cell and output a specific figure, for example.

    if A2 = (the range of numbers between 100 to 299) then answer is 3
    if A2 = (the range of numbers between 300 to 499) then answer is 4

    so in other words if i put the figure 105 in cell A2 then the answer would be 3
    and also if i put the figure 405 in cell A2 the answer would be 4

    I hope i explained this properly!

    any help would be greatly appreciated
    Thank you in advance,

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Output a specific number from range of numbers in one cell

    This can be more simplified based on your requirement

    =if(and(A2>100,<299),3,if(and(A2>300,A2<499),4,"")

    Punnam

  3. #3
    Registered User
    Join Date
    09-17-2014
    Location
    Trinidad
    MS-Off Ver
    Ms Office 2010
    Posts
    3

    Re: Output a specific number from range of numbers in one cell

    i'am sorry but that formula does not work.

  4. #4
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Output a specific number from range of numbers in one cell

    Check this
    Formula in B2
    =IF(AND(A2>100,A2<299),3,IF(AND(A2>300,A2<499),4,""))
    Punnam

  5. #5
    Registered User
    Join Date
    09-17-2014
    Location
    Trinidad
    MS-Off Ver
    Ms Office 2010
    Posts
    3

    Re: Output a specific number from range of numbers in one cell

    Thank you for your help
    i got it to work using a modification of your formula,
    with your formula i was getting an output of 0 when 100,299,300,499 is entered

    This is what i did:

    =IF(AND(A2>=100;A2<=299);3;IF(AND(A2>=300;A2<=499);4; ))

    i see you are using Office 2007 and iam using Office 2010 so i had to change all the "," to ";"
    also i added the "=" sign so i can get an output when entering 100,299,300,499

  6. #6
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Output a specific number from range of numbers in one cell

    @ Sigmaiota,
    Yes you are correct i have not observed that you are using MS 2010.
    Nice job .
    If this takes cares of your post . mark the thread as solved using the thread tools & u can also thank one who helped you by adding Reputation points by clicking the star button .
    Punnam

+ 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. [SOLVED] Macro to print number of copies equal to the output of a formula in a specific cell
    By Spencerk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2014, 03:54 PM
  2. Rounding a range of numbers to a specific number using an IF statement
    By Jpeppa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2012, 10:21 PM
  3. Count the number between specific Range of numbers
    By lavan_joy in forum Excel General
    Replies: 3
    Last Post: 01-19-2012, 11:00 AM
  4. Replies: 2
    Last Post: 08-06-2008, 02:09 PM
  5. Replies: 5
    Last Post: 07-01-2008, 09:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1