Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PickList - numbers (Excel 2000+)

    I am curious whether anyone has used PickList on pure numeric data, rather than on string data.

    This caught me by surprise yesterday - While I regularly use right-click PickList on a vertical block of sting data, it appears not to work on a vertical block of numeric data.

    I can enter the numeric data as text, for example, preceding each digit-string with a single-quote character - and PickList from that table. (Alignment-right helps)
    The resultant cell holds the value as a numeric (right-aligned) rather than as a text value.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PickList - numbers (Excel 2000+)

    Are you referring to the Data, Validation option?
    If so, you can simply add a list of numbers and they will act as such.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PickList - numbers (Excel 2000+)

    >Are you referring to the Data, Validation option?
    Thanks Kan Karel, but no, I was referring to (Excel 2000) right-click, "Pick From List"

    In the attached workbook Sheet1, select cell A6 and then right-click, pick From List. Works for me.
    In the attached workbook Sheet1, select cell B6 and then right-click, pick From List. Comes up empty.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PickList - numbers (Excel 2000+)

    I think the problem is that to XL a list is only the text values since they are categorical. Numbers are not categorical.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PickList - numbers (Excel 2000+)

    >they are categorical. Numbers are not categorical.
    Er, Steve, Thanks, I think.(grin)

    I thought I knew how to use "categorical", until I read your reply.
    Might you explain in a bit more detail?
    It's apparent to me that numbers do NOT constitute a list in XL2000 in the sense that PickFromList does not recognize them as such.
    I have to live with that.

    I'm puzzled as to why - logically - a designer might choose to ignore/exclude them.

    I'm not asking you to redesign Xl (although you'd probably do a better job), but can you come up with a good reason to exclude numeric values?

    I've been scratching my head for 24 hours and can't find a reason NOT to include the results of formulae, as well as hard-coded numeric values.

    The attached updated book shows interesting behaviour for columns C and D.
    Excel 200 is happy with formulae that deliver a text result.

    Column E is even more fun.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PickList - numbers (Excel 2000+)

    A list of Numbers are not "distinct set entities", they have variability, they are "continuous". I use the terms in a "statistical sense" like in factors in an experiment. I can vary the temperature from 10 - 50 and have some at 10, some at 20 some at 50, etc and when I am done analyzing, I can predict on optimum temperature and it may not be on that I ran. I could get the optimum at 25 without running it.

    But with a categorical variable (like a Supplier name) they are distinct, A, B, C there is nothing between A and B or B and C or A and C.

    I don't know if excel this as an issue, but it is what comes to my mind in the differences between numbers and text entries and explains the difference you observe in the picklist behavior

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PickList - numbers (Excel 2000+)

    >differences between numbers and text entries
    I think I understand your use of the word "categorical" now. It would be akin to "discrete" or "unrelated". "placed in a class or division", says my dictionary.

    I still can't think of a good reason for excluding numeric values from Pick From List within a spreadsheet, since by definition, values in a spreadsheet are in discrete cells. (I try to imagine myself teaching a class and being asked by attendees why this is so; and I hate not to have an answer!)

    I can dream up some air-code in VBA to implement a picklist with numbers, or mixed text and numbers.
    Indeed, Microsoft has developed code (Data Validation) that deals with mixed types, as the attached workbook shows.

    I think the reason that PickList only works on strings is that the programmer had a colossal failure of nerve and decided not to accept numerics.
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PickList - numbers (Excel 2000+)

    Yes that is how I was using categorical.

    Now whether it is good reason, or even the reason, I don't know. I was only trying to explain the fact that XL does it. It appears that for whatever reason, the picklist works explicitly only with text. The simple answer is that it was programmed this way, but the logic as to why I don't know and it is tough to hazard more of a guess without even seeing how it is coded...

    Your explanation may even be more likely <img src=/S/smile.gif border=0 alt=smile width=15 height=15>...

    But in other cases it does not distinguish them. Autofilter, for example, treats everything as text and as the text is displayed. If picklist was done this way, you would pick a number and it would become text which would be annoying. It may be that since the picklist box is a text box, it could not distinguish (like autofilter) between a text "1" and the number 1 so the programming could not tell what you wanted so they chose to stick with only things that were text...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •