IF function

[wirman]

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(I40/1000000,0),2)),read_2!$A$1:$B$99,2,FALSE),"")&"million
","")&IF(MOD(I40,1000000)<1000,"",IF(MOD(I40,1000000)>=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(I40,1000),1)),read_2!$A$1:$B$99,2,FALSE)&"hundre
d
","")&IF(MOD(I40,100)>=1,VLOOKUP(VALUE(RIGHT(MOD(I40,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

 

[Eduardo]

Hi,
take a look here,

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

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

code is open

 

[wirman]

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...