Number-Word Formula Problem.

[richie]

Hello everyone.
I'm trying to develop a formula to interpret a two digit number and display its word representation (in spanish, by the way).
Since the numbers will develop in more than two-digit, I chose to do each digit in a different cell.
I've run into a funky problem in the last part of the formula, where it needs to check that, if the unity digit is not 1 or 2, it should choose from a list of values to display.
The actual problem is that if I add more values after "treinta" the cell stops working. Even if y add one more character to "treinta" (as in "treintax"), it will stop working.
I hope I'm being clear. Does anyone have an insight on this issue?

This is the (problematic) cell for the tenths (ten, twenty, thirty, etc), and responds to a cell named "d" where I manually input a digit.

IF(OR(d=1,d=2)=1,IF(d=1,IF(u=0,"diez",IF(u>5,"dieci",
CHOOSE(u,"once","doce","trece","catorce","quince"))),IF(AND(d=2,u=0),"veinte","veinti")),
CHOOSE(d-2,"treinta"))


This is the cell for the units. It reacts to a cell named "u" where I manually input a digit, and it is necessary for the arguments in cell "d" to work.

CONCAT(IF(d>1,IF(u=0,""," y "),""),IF(OR(u=0,AND(d=0,u<1),AND(d=1,u<6)),"",
CHOOSE(u,"un","dos","tres","cuatro","cinco","seis","siete","ocho","nueve")))



Thanks for any help.


Rich

NOTE: Formulas edited (return added before CHOOSE) to reduce width for viewing. Remove returns before using formulas.
B.

[Barry]

Hello everyone.
I'm trying to develop a formula to interpret a two digit number and display its word representation (in spanish, by the way).
Since the numbers will develop in more than two-digit, I chose to do each digit in a different cell.
I've run into a funky problem in the last part of the formula, where it needs to check that, if the unity digit is not 1 or 2, it should choose from a list of values to display.
The actual problem is that if I add more values after "treinta" the cell stops working. Even if y add one more character to "treinta" (as in "treintax"), it will stop working.
I hope I'm being clear. Does anyone have an insight on this issue?

This is the (problematic) cell for the tenths (ten, twenty, thirty, etc), and responds to a cell named "d" where I manually input a digit.

IF(OR(d=1,d=2)=1,IF(d=1,IF(u=0,"diez",IF(u>5,"dieci",
CHOOSE(u,"once","doce","trece","catorce","quince"))),IF(AND(d=2,u=0),"veinte","veinti")),
CHOOSE(d-2,"treinta"))


This is the cell for the units. It reacts to a cell named "u" where I manually input a digit, and it is necessary for the arguments in cell "d" to work.

CONCAT(IF(d>1,IF(u=0,""," y "),""),IF(OR(u=0,AND(d=0,u<1),AND(d=1,u<6)),"",
CHOOSE(u,"un","dos","tres","cuatro","cinco","seis","siete","ocho","nueve")))



Thanks for any help.


Rich

Hi Rich,

I solved this problem for English language numbers (up to ninety nine billion, nine hundred ninety nine million, nine hundred ninety nine thousand, nine hundred ninety nine, the largest possible wthin AppleWorks's 11 digit precision) some time ago.

The basic steps in my technique were to:

1. Convert the number (in A2 for this example)to a text string consisting of the number and enough leading spaces to make the string 12 characters long. The result is stored in C2.

=RIGHT(" "&INT(A2),12)
Note: 12 spaces between the quotes.



2. Separate the resulting string into its individual characters, storing the characters in cells H2..J2, L2..N2, P2..R2 and T2..V2.

H2: =MID($C2,1,1) .. V2: =MID($C2,12,1)


3. Convert each of the characters to the corresponding word, using a lookup table, and placing each word into a separate cell Immediately below the character (ie. H3..V3).


H3: =IF(H2<>" ",LOOKUP(H2,$AF$2..$AF$11,$AG$2..$AG$11),"")

I3: =IF(I2<>" ",LOOKUP(I2,$AF$2..$AF$11,$AH$2..$AH$11),"")

J3: =IF(J2<>" ",LOOKUP(J2+10*(I2=1),$AF$2..$AF$21,$AI$2..$AI$21),"")

K3: =IF(OR(H2>0,I2>0,J2>0),"billion, ","")

The same four formulas are repeated for the millions and thousands periods (with the word in the last changed to "million, " then to "thousand, "), and the first three again for the hundreds, tens and units columns. Note the comma and space after the word in the last formula.

The change in the third formula is to handle numbers with a 1 in the 10s place (or 10 thousands place, etc.) such as 214.

LOOKUP table:

Column 1 (lookup values): (AF2..AF21) contains the numbers from 0 to 19 in ascending order.

Column 2 (hundreds): (AG2..AG11) contains an empty cell (zero) and the words "one hundred " to "nine hundred " (note the space following each word. There is NO space nor anything else in the zero cell.

Column 3 (tens): (AH2..AH11) Both the first (zero) and the second (one) cells are empty; the rest contain the words "twenty " through "ninety " (again followed by a single space.

Column 4 (ones and teens): (AI2..AI21) The zero cell is empty. The rest contain the words "one " through "nineteen " (each followed by a single space)

4. Concatenate the contents of cells H3 through V3, and place the result (the number in C2 translated into its word equivalent) in G3.

G3: =H3&I3&J3&K3&L3&M3&N3&O3&P3&Q3&R3&S3&T3&U3&V3

Besides whole numbers up to 11 digits, the spreadsheet I developed will handle decimals to three places. If you'd like a copy, email me directly, using the address available in my user profile. Please put AppleWorks Number-Word in the subject line, and specfy the AW 5 or AW 6 version.

Regards,
Barry

[richie]

Wow Barry.
Thanks for sharing your coding. I had already started working on breaking the number into characters, and ran into the problem that your twelve-space solution addreses, so thousand-thanks for it.
Unfortunately, since what I ultimately want to do is incorporate these formulas into a DataBase, I can't make use of the LOOKUP function.

While analysing the problematic formula I posted before, I found out I could get rid of "AND(d=2," and the corresponding ")". Erasing these nine characters allowed me to add "cincuent" to the values of CHOOSE. The formula ended like this:

IF(OR(d=1,d=2)=1,IF(d=1,IF(u=0,"diez",IF(u>5,"dieci",
CHOOSE(u,"once","doce","trece","catorce","quince"))),IF(u=0,"veinte","veinti")),
CHOOSE(d-2,"treinta","cuarenta","cincuent"))

If I write the whole word "cincuenta", I get a #ARG! error. So maybe I'm hitting one limit that AppleWorks imposes. There are 177 characters so far in this formula, but I understand AW can handle more than 200... so I'm stumped.


Thanks,


Rich

NOTE: Formulas edited (return added before CHOOSE) to reduce width for viewing. Remove returns before using formulas.
B.

[Barry]

Wow Barry.
Thanks for sharing your coding. I had already started working on breaking the number into characters, and ran into the problem that your twelve-space solution addreses, so thousand-thanks for it.
Unfortunately, since what I ultimately want to do is incorporate these formulas into a DataBase, I can't make use of the LOOKUP function.

While analysing the problematic formula I posted before, I found out I could get rid of "AND(d=2," and the corresponding ")". Erasing these nine characters allowed me to add "cincuent" to the values of CHOOSE. The formula ended like this:

IF(OR(d=1,d=2)=1,IF(d=1,IF(u=0,"diez",IF(u>5,"dieci",
CHOOSE(u,"once","doce","trece","catorce","quince"))),IF(u=0,"veinte","veinti")),
CHOOSE(d-2,"treinta","cuarenta","cincuent"))

If I write the whole word "cincuenta", I get a #ARG! error. So maybe I'm hitting one limit that AppleWorks imposes. There are 177 characters so far in this formula, but I understand AW can handle more than 200... so I'm stumped.

AW formulas length limit is 255 characters, but functions count for more characters than actually appear on the page. Since you're using 8 functions (5 IF(), 2 CHOOSE() and 1 OR()), this may account for the error.

Here's one edit that should shorten the formula by 1 character (or more, if the = operator also counts as more than a single character). It removes the two [bold] characters in your formula, quoted above.

IF(OR(d=1,d=2),IF(d=1,IF(u=0,"diez",IF(u>5,"dieci",
CHOOSE(u,"once","doce","trece","catorce","quince"))),IF(u=0,"veinte","veinti")),
CHOOSE(d-2,"treinta","cuarenta","cincuenta"))

Another way to handle this would be to split the job between a number of fields. The field containing the above formula would handle d>2, and would hand off the job to one of two other fields for d=1 and d=2.

For above field:
CHOOSE(d,'d1','d2',"treinta","cuarenta","cincuenta")

This should give you enough room to extend this formula to handle numbers into the nineties.

New field 'd1' handles the numbers with 1 in the tens place.

CHOOSE(u+1,"diez","once","doce","trece","catorce","quince",...)

New field 'd2' handles numbers with 2 in the tens place.

CHOOSE(u+1,"veinte","veinti",...)

Be aware that I haven't considered numbers like 16 or 23 in these, as I'm not familiar with Spanish numeration. 'd1' and 'd2' should be written to handle all 10 cases where the tens digit is 1 or 2.

I assume you've already solved how to handle the ones digit in these cases.

Regards,
Barry

NOTE: Formulas edited (return added before CHOOSE) to reduce width for viewing. Remove returns before using formulas.
B.