+ Reply to Thread
Results 1 to 8 of 8

Range of numbers in single cell

  1. #1
    Registered User
    Join Date
    11-09-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2008
    Posts
    4

    Range of numbers in single cell

    Hello everyone,

    I have an issue, that i am trying to solve, and while spending two days browsing forums and help pages, i still haven't managed to find a viable solution for my case.

    My issue is the following:

    I am creating an excel sheet that uses conditional formatting to format cells, if the number inside them is between a defined range of values. I have 11 conditional format conditions. Now, this all works fine, but i am trying to make this possible for users that do not have any conditional formatting experience with excel, to be able to define in cells the range they want for each condition themselves. The coloring will not be variable, but the range will be. So it comes down to being able to have a single cell having a range of values and not a single value, so that then conditional formatting can look into that cell to see what it will format and how.

    My problem is, that no matter how much i looked i haven't found yet a way to make a single cell have a range of numbers, without having those numbers pre-typed (as in A1 to A500 for example). This can be messy because the range i am looking at needs to contain 3 to 4 decimals, so typing in all those numbers even between 1 and 5 would be a mess.

    I hope i explained my problem enough, and i am really eager to hear your ideas on this one.

    Thanks for your time.

    PS1: Multiple/nested IFs will not work as i have more than 7 conditions…maybe something with VLOOKUP?
    PS2: I am working on a Mac, so no VBA code pls. (it is not possible to sue it here). I am just looking for a function to have all the numbers from 1 to 5 as a value in a single cell.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Range of numbers in single cell

    Hi Silverspell,

    a workbook can say more than a thousand words. Could you possibly upload a small data sample that illustrates what you want to achieve? Highlight input, processing logic and expected output.

    Click "Go Advanced" below and then the paper clip icon to attache a file.

    cheers

  3. #3
    Registered User
    Join Date
    11-09-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2008
    Posts
    4

    Re: Range of numbers in single cell

    Hello teylyn and thanks for your quick reply,

    Here is a simple workbook.

    INPUT is designated with gray background. A column is minimum number of the range and B column is the maximum/equal value. I need a function that C1 (OUTPUT/yellow) contains all the numbers between A1 and B1. This way a user can set each time A and B column values, and then conditional formatting can look at C for the range.

    Thanks
    Attached Files Attached Files

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Range of numbers in single cell

    what would you expect in C1 and why?

    all the numbers between A1 and B1
    A1 and B1 have column headers

    A2 has 50 and B2 is empty. So C... what? C1? C2? should be... what... why? Evenif there were values, all the numbers between two numbers is a pretty big ask. How many decimals?

    Please, some more explanation
    Last edited by teylyn; 11-10-2009 at 06:17 AM.

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Range of numbers in single cell

    If you're looking to define parameters for your conditional fomat, why not use something like

    AND(X1>=A1,X1<=B1)

    that covers all values between A1 and B1

  6. #6
    Registered User
    Join Date
    11-09-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2008
    Posts
    4

    Re: Range of numbers in single cell

    Yes C1 was a mistype, i apologize. It is a Header.

    C2 i would like any number above 50 (that is why i left Max/B2 empty). C3 All numbers between A3 and B3. As i said in my original post, 3 to 4 decimals should be enough.

    The whole idea behind this is: as conditional formatting has the setting "Between 10 and 50" to color the background of a cell, i want this setting as a value in a cell - thus i am looking to set a single cell having a whole range of numbers rather than a single number. I am not sure if it is at all possible.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Range of numbers in single cell

    as conditional formatting has the setting "Between 10 and 50"
    Formula IS =AND(X1>=A1,X1<=B1)

    where A1 has 10 and B1 has 50. No need to list all possible individual numbers with up to four decimals individually.

    Or am I not getting it?

  8. #8
    Registered User
    Join Date
    11-09-2009
    Location
    Netherlands
    MS-Off Ver
    Excel 2008
    Posts
    4

    Re: Range of numbers in single cell

    Yes you are getting it

    Thanks a lot, it works like a charm. This "AND" made all the difference.

    I appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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