IF function
 

hi, I got this problem when I tried to make a function for reading numbers
and translate it into words. like this code below:

=IF(I40=1000000,IF(ROUNDDOWN(I40/1000000,0)=100,VLOOKUP(VALUE(LEFT(ROUNDDOWN(I40/1000000,0),1)),read_2!$A$1:$B$99,2,FALSE)&"hundred
","")&IF(VALUE(RIGHT(ROUNDDOWN(I40/1000000,0),2))=1,VLOOKUP(VALUE(RIGHT(ROUNDDOWN(I4 0/1000000,0),2)),read_2!$A$1:$B$99,2,FALSE),"")&"mil lion
","")&IF(MOD(I40,1000000)<1000,"",IF(MOD(I40,10000 00)=100000,VLOOKUP(VALUE(LEFT(MOD(I40,1000000),1)
),read_2!$A$1:$B$99,2,FALSE)&"hundred
","")&IF(MOD(I40,100000)=10000,VLOOKUP(VALUE(LEFT (MOD(I40,100000),2)),read_2!$A$1:$B$99,2,FALSE),IF
(MOD(I40,100000)=1000,VLOOKUP(VALUE(LEFT(MOD(I40, 100000),1)),read_2!$A$1:$B$99,2,FALSE),""))&"thous
and
")&IF(MOD(I40,1000)=100,VLOOKUP(VALUE(LEFT(MOD(I4 0,1000),1)),read_2!$A$1:$B$99,2,FALSE)&"hundre
d
","")&IF(MOD(I40,100)=1,VLOOKUP(VALUE(RIGHT(MOD(I 40,100),2)),read_2!$A$1:$B$99,2,FALSE),"")&")"

I tried this code manually (by entering the numbers on my own)and it works!
but the problem is when I apply it into a formulated cell (a cell that
containing =sum())

for example:

if the cell contain the value of 1,689,750

it would be read into:

"one million six hundred eighty nine thousand seven hundred seventy one"

it's not read as "fifty".

and, I made a sheet (data table) for "translate" numbers into words in a
range of 1 - 99. and is called "read_2"

this is so confusing because i didn't find an error for my code because when
I tried to enter it manually, it shows a normal result.

I'm waiting for your replies

thanks

IF function
 

Hi,
take a look here,

This file might be a help:
http://www.bygsoftware.com/examples/...s/num2wrds.zip

http://www.bygsoftware.com/examples/examples.htm

code is open

"wirman" wrote:

hi, I got this problem when I tried to make a function for reading numbers
and translate it into words. like this code below:

=IF(I40=1000000,IF(ROUNDDOWN(I40/1000000,0)=100,VLOOKUP(VALUE(LEFT(ROUNDDOWN(I40/1000000,0),1)),read_2!$A$1:$B$99,2,FALSE)&"hundred
","")&IF(VALUE(RIGHT(ROUNDDOWN(I40/1000000,0),2))=1,VLOOKUP(VALUE(RIGHT(ROUNDDOWN(I4 0/1000000,0),2)),read_2!$A$1:$B$99,2,FALSE),"")&"mil lion
","")&IF(MOD(I40,1000000)<1000,"",IF(MOD(I40,10000 00)=100000,VLOOKUP(VALUE(LEFT(MOD(I40,1000000),1)
),read_2!$A$1:$B$99,2,FALSE)&"hundred
","")&IF(MOD(I40,100000)=10000,VLOOKUP(VALUE(LEFT (MOD(I40,100000),2)),read_2!$A$1:$B$99,2,FALSE),IF
(MOD(I40,100000)=1000,VLOOKUP(VALUE(LEFT(MOD(I40, 100000),1)),read_2!$A$1:$B$99,2,FALSE),""))&"thous
and
")&IF(MOD(I40,1000)=100,VLOOKUP(VALUE(LEFT(MOD(I4 0,1000),1)),read_2!$A$1:$B$99,2,FALSE)&"hundre
d
","")&IF(MOD(I40,100)=1,VLOOKUP(VALUE(RIGHT(MOD(I 40,100),2)),read_2!$A$1:$B$99,2,FALSE),"")&")"

I tried this code manually (by entering the numbers on my own)and it works!
but the problem is when I apply it into a formulated cell (a cell that
containing =sum())

for example:

if the cell contain the value of 1,689,750

it would be read into:

"one million six hundred eighty nine thousand seven hundred seventy one"

it's not read as "fifty".

and, I made a sheet (data table) for "translate" numbers into words in a
range of 1 - 99. and is called "read_2"

this is so confusing because i didn't find an error for my code because when
I tried to enter it manually, it shows a normal result.

I'm waiting for your replies

thanks

IF function
 

hi there,i've seen your file. that's a good file. but actually I want to have
a non-VBA function. coz actually i think: it is not necessary to use them
(macros). i'm sure there's gotta be a way to have that function without using
VBAs. but your file it's quite helpful, though. it's a good tutorial indeed...

"Eduardo" wrote:

Hi,
take a look here,

This file might be a help:
http://www.bygsoftware.com/examples/...s/num2wrds.zip

http://www.bygsoftware.com/examples/examples.htm

code is open

"wirman" wrote:

hi, I got this problem when I tried to make a function for reading numbers
and translate it into words. like this code below:

=IF(I40=1000000,IF(ROUNDDOWN(I40/1000000,0)=100,VLOOKUP(VALUE(LEFT(ROUNDDOWN(I40/1000000,0),1)),read_2!$A$1:$B$99,2,FALSE)&"hundred
","")&IF(VALUE(RIGHT(ROUNDDOWN(I40/1000000,0),2))=1,VLOOKUP(VALUE(RIGHT(ROUNDDOWN(I4 0/1000000,0),2)),read_2!$A$1:$B$99,2,FALSE),"")&"mil lion
","")&IF(MOD(I40,1000000)<1000,"",IF(MOD(I40,10000 00)=100000,VLOOKUP(VALUE(LEFT(MOD(I40,1000000),1)
),read_2!$A$1:$B$99,2,FALSE)&"hundred
","")&IF(MOD(I40,100000)=10000,VLOOKUP(VALUE(LEFT (MOD(I40,100000),2)),read_2!$A$1:$B$99,2,FALSE),IF
(MOD(I40,100000)=1000,VLOOKUP(VALUE(LEFT(MOD(I40, 100000),1)),read_2!$A$1:$B$99,2,FALSE),""))&"thous
and
")&IF(MOD(I40,1000)=100,VLOOKUP(VALUE(LEFT(MOD(I4 0,1000),1)),read_2!$A$1:$B$99,2,FALSE)&"hundre
d
","")&IF(MOD(I40,100)=1,VLOOKUP(VALUE(RIGHT(MOD(I 40,100),2)),read_2!$A$1:$B$99,2,FALSE),"")&")"

I tried this code manually (by entering the numbers on my own)and it works!
but the problem is when I apply it into a formulated cell (a cell that
containing =sum())

for example:

if the cell contain the value of 1,689,750

it would be read into:

"one million six hundred eighty nine thousand seven hundred seventy one"

it's not read as "fifty".

and, I made a sheet (data table) for "translate" numbers into words in a
range of 1 - 99. and is called "read_2"

this is so confusing because i didn't find an error for my code because when
I tried to enter it manually, it shows a normal result.

I'm waiting for your replies

thanks