*Google Spreadsheet Post #1900*

*Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Feb-20-2015*

Note:

formula in this blog post is for Google New Sheets

Earlier in my following blog post I had formulation that worked in so called Google Old Sheets

**https://productforums.google.com/forum/#!mydiscussions/docs/kNTxdJA_QEI**how do I get a cell reflect "eight" when the formula is 4+4 instead of =8

how do I get a cell reflect "eight" when the formula is 4+4 instead of =8

---

This might be a better explanation.

...
The following formula =

(IF(OR(H3>1000000,H3<=0),"",IF( H3<1000,"",IF(MOD(H3,1000000)>= 100000,INDEX(TRUNC(MOD(H3,1000000 )/100000,0))&" Hundred","")&IF(MOD(H3,100000)>= 20000," "&INDEX(TRUNC(MOD(H3,100000)/10000 ,0))&IF(MOD(MOD(H3,100000), 10000)>=1000,"-"&INDEX(TRUNC(MOD (MOD(H3,100000),10000)/1000, 0)),""),IF(MOD(H3,100000)>=1000 ," "&INDEX(TRUNC(MOD(H3,100000)/1000 ,0)),""))&" Thousand") & IF(H3<1,"Zero Dollars",IF(MOD(H3,1000)>=100," " &INDEX(TRUNC(MOD(H3,1000)/100, 0))&" Hundred",""

=IF(OR(H3>1000000,H3<=0),"",IF(H3<1000,"",IF(MOD(H3,1000000)>=100000,INDEX( numbers,TRUNC (MOD(H3,1000000)/100000,0)+1)&" Hundred","")&IF(MOD(H3,100000)>=20000," "&INDEX(tens,TRUNC(MOD(H3,100000)/10000,0)+1)&IF(MOD(MOD(H3,100000),10000)>=1000,"-"&INDEX(numbers,TRUNC(MOD(MOD(H3,100000),10000)/1000,0)+1),""),IF(MOD(H3,100000)>=1000," "&INDEX(numbers,TRUNC(MOD(H3,100000)/1000,0)+1),""))&" Thousand") & IF(H3<1,"Zero Dollars",IF(MOD(H3,1000)>=100," "&INDEX(numbers,TRUNC(MOD(H3,1000)/100,0)+1)&" Hundred","")&IF(MOD(H3,100)>=20," "&INDEX(tens,TRUNC(MOD(H3,100)/10,0)+1)&IF(MOD(MOD(H3,100),10)>=1,"-"&INDEX(numbers,TRUNC(MOD(MOD(H3,100),10),0)+1),""),IF(MOD(H3,100)>=1," "&INDEX(numbers,TRUNC(MOD(H3,100),0)+1),""))&"") & " and " & IF(MOD(H3,1)<0.005,"NO",ROUND(MOD(H3,1)*100,0)) & "/100 -----------")

See attached file for what I am trying to achieve. When I fill the amount box with $550.25 the amount written comes as "5 Hundred 5 and 25/100 -----------" instead of "Five Hundred and Fifty Dollars and 25/100-----------"

The attached file depicts the use of the formula in my previous post.

I hope this is more clear

Attachments (1)