Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mapping integers to strings
I run Excel 2003 on a Windows Vista system.
I have an application in which I want to map non-negative integers to text. I tried using OFFSET. A B C D E 1 Clubs Dimonds Hearts Spades NoTrump 2 0 Clubs B2 is =OFFSET($A$1,0,$A$2,1,1) I use 0 for cols to show the problem with the next part. If B3 is =OFFSET({"Clubs"},0,$A$2,1,1), I hoped to do something similar with a constant array but get "The formula you typed contains an error". I accepted the error that Excel gives me because OFFSET help says: "OFFSET(reference,rows,cols,height,width) Reference is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value." It seems, Excel does not accept an array constant as equivalent to a cell or range of cells in an OFFSET call. I think it should as "About array formulas and array constants" help says "Array constants can be used in place of references when you don't want to enter each constant value in a separate cell on the worksheet". I am looking for a better way of including array constants within formulas, rather than in cells. I don't like =IF($A$2=0,"Clubs",IF($A$2=1,"Dimonds",IF($A$2=2," Hearts",IF($A$2=3,"Spa des","NoTrump")))) or =IF($A$2<2,IF(A2=0,"Clubs","Dimonds"),IF($A$2<4,IF ($A$2=2,"Hearts","Spad es"),"NoTrump")) and I don't want a user-defined function (UDF) either. I am looking for some simple means to get a member from a constant array in a worksheet. e.g. something like ={"Clubs"}(0) or ={"Clubs"}(1). -- Walter Briscoe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mapping integers to strings
Hi Walter,
Am Tue, 25 Jun 2013 07:33:20 +0100 schrieb Walter Briscoe: I am looking for some simple means to get a member from a constant array in a worksheet. e.g. something like ={"Clubs"}(0) or ={"Clubs"}(1). what about: =CHOOSE(A2+1,"Clubs","Diamonds","Hearts","Spades", "NoTrump") or HLOOKUP: =HLOOKUP(A2,{0,1,2,3,4;"Club","Diamonds","Hearts", "Spades","NoTrump"},2,TRUE) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mapping integers to strings
Hi Walter,
Am Tue, 25 Jun 2013 09:13:33 +0200 schrieb Claus Busch: =CHOOSE(A2+1,"Clubs","Diamonds","Hearts","Spades", "NoTrump") or HLOOKUP: =HLOOKUP(A2,{0,1,2,3,4;"Club","Diamonds","Hearts", "Spades","NoTrump"},2,TRUE) or: =INDEX(1:1,1,A2+1) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mapping integers to strings
On Tue, 25 Jun 2013 07:33:20 +0100, Walter Briscoe wrote:
I run Excel 2003 on a Windows Vista system. I have an application in which I want to map non-negative integers to text. I tried using OFFSET. A B C D E 1 Clubs Dimonds Hearts Spades NoTrump 2 0 Clubs I am looking for a better way of including array constants within formulas, rather than in cells. You can use the INDEX function with an array constant: =INDEX({"Clubs","Diamonds","Hearts","Spades","NoTr ump"},A2+1) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mapping integers to strings
On Tue, 25 Jun 2013 07:33:20 +0100, Walter Briscoe
wrote: I run Excel 2003 on a Windows Vista system. I have an application in which I want to map non-negative integers to text. I tried using OFFSET. A B C D E 1 Clubs Dimonds Hearts Spades NoTrump 2 0 Clubs B2 is =OFFSET($A$1,0,$A$2,1,1) I use 0 for cols to show the problem with the next part. If B3 is =OFFSET({"Clubs"},0,$A$2,1,1), I hoped to do something similar with a constant array but get "The formula you typed contains an error". I accepted the error that Excel gives me because OFFSET help says: "OFFSET(reference,rows,cols,height,width) Reference is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value." It seems, Excel does not accept an array constant as equivalent to a cell or range of cells in an OFFSET call. I think it should as "About array formulas and array constants" help says "Array constants can be used in place of references when you don't want to enter each constant value in a separate cell on the worksheet". I am looking for a better way of including array constants within formulas, rather than in cells. I don't like =IF($A$2=0,"Clubs",IF($A$2=1,"Dimonds",IF($A$2=2, "Hearts",IF($A$2=3,"Spa des","NoTrump")))) or =IF($A$2<2,IF(A2=0,"Clubs","Dimonds"),IF($A$2<4,I F($A$2=2,"Hearts","Spad es"),"NoTrump")) and I don't want a user-defined function (UDF) either. I am looking for some simple means to get a member from a constant array in a worksheet. e.g. something like ={"Clubs"}(0) or ={"Clubs"}(1). You could crate a lookup table and give it a range name and use vlookups to grab the text attached to an integer used in other cells, etc. Number Text 1 Diamonds 2 Clubs 3 Hearts 4 Spades My banner painter workbook uses numeric-to-text tables and numeric-to-acronym type conversions, lookups, and conditionals... See if it helps: http://www.mediafire.com/view/ssmn26...erPainter.xlsx |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mapping integers to strings
In message of Tue, 25 Jun
2013 03:41:25 in microsoft.public.excel.worksheet.functions, CellShocked <cellshocked@thecellvalueattheendofthespreadsheet. org writes On Tue, 25 Jun 2013 07:33:20 +0100, Walter Briscoe wrote: I run Excel 2003 on a Windows Vista system. I have an application in which I want to map non-negative integers to text. I tried using OFFSET. A B C D E 1 Clubs Dimonds Hearts Spades NoTrump 2 0 Clubs B2 is =OFFSET($A$1,0,$A$2,1,1) I use 0 for cols to show the problem with the next part. If B3 is =OFFSET({"Clubs"},0,$A$2,1,1), I hoped to do something similar with a constant array but get "The formula you typed contains an error". I accepted the error that Excel gives me because OFFSET help says: "OFFSET(reference,rows,cols,height,width) Reference is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value." It seems, Excel does not accept an array constant as equivalent to a cell or range of cells in an OFFSET call. I think it should as "About array formulas and array constants" help says "Array constants can be used in place of references when you don't want to enter each constant value in a separate cell on the worksheet". I am looking for a better way of including array constants within formulas, rather than in cells. I don't like =IF($A$2=0,"Clubs",IF($A$2=1,"Dimonds",IF($A$2=2 ,"Hearts",IF($A$2=3,"Spa des","NoTrump")))) or =IF($A$2<2,IF(A2=0,"Clubs","Dimonds"),IF($A$2<4, IF($A$2=2,"Hearts","Spad es"),"NoTrump")) and I don't want a user-defined function (UDF) either. I am looking for some simple means to get a member from a constant array in a worksheet. e.g. something like ={"Clubs"}(0) or ={"Clubs"}(1). You could crate a lookup table and give it a range name and use vlookups to grab the text attached to an integer used in other cells, etc. Number Text 1 Diamonds 2 Clubs 3 Hearts 4 Spades My banner painter workbook uses numeric-to-text tables and numeric-to-acronym type conversions, lookups, and conditionals... See if it helps: http://www.mediafire.com/view/ssmn26...erPainter.xlsx Dear CellShocked, My thanks to you, Claus Busch and Ron Rosenfeld for your valuable contributions. I now have several options; all look good. I downloded your ColorBannerPainter.xlsx. Excel 2003 has conversion issues with it. "This file was created in a newer version of Microsoft Excel. The file has been converted to a format you can work with, but the following issues were encountered. The file has been opened in read-only mode to protect the original file. - Some cells have more conditional formats than are allowed in this version of Excel. Only the first three conditions will be displayed. - Some cells have overlapping conditional formatting ranges. This version of Excel will not evaluate all the conditional formatting rules on the overlapping cells. - Some cells contain multiple conditional formatting rules that should all be evaluated and shown. This version of Excel does not have this option, and will stop evaluation after the first true condition." I will port the file to my son's machine. He has a more modern version than 2003; I think 2010. Does the behavior, I reported with OFFSET exist in newer versions [2003+] of Microsoft Excel? -- Walter Briscoe |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mapping integers to strings
Hi Walter,
Am Tue, 25 Jun 2013 13:03:04 +0100 schrieb Walter Briscoe: Does the behavior, I reported with OFFSET exist in newer versions [2003+] of Microsoft Excel? the first argument (base) of OFFSET has to be a reference. For your wishes you can use it so: =OFFSET($A$1,,A2) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Seems there is a bug with long integers in VBA | Excel Programming | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
A list of Consecutive Integers, can I search for missing integers | Excel Worksheet Functions |