Mapping integers to strings

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

Walter Briscoe

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).
 
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).
Click to expand...

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
 
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)
Click to expand...

or:
=INDEX(1:1,1,A2+1)


Regards
Claus Busch
 
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).
Click to expand...

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/ssmn26xs56zs2z2/ColorBannerPainter.xlsx
 
In message <[email protected]> of Tue, 25 Jun
2013 03:41:25 in microsoft.public.excel.worksheet.functions, CellShocked
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/ssmn26xs56zs2z2/ColorBannerPainter.xlsx
Click to expand...

Dear CellShocked,
My thanks to you, Claus Busch <[email protected]> and Ron
Rosenfeld <[email protected]> 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?
 
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?
Click to expand...

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
 
Back
Top