Wednesday, February 25, 2015

yogi_Spell Out An Amount Say Currency Such As Dollar And Cents As Would Be On A Check -- For Google New Sheets

             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
yogi_Spell Out An Amount Say Currency Such As Dollar And Cents As Would Be On A Check
http://yogi--anand-consulting.blogspot.com/2012/11/yogispell-out-dollar-and-cents-as-would.html
instead of trouble shooting and fixing the formula i rewrote it as presented in this blog post

question by Mitrih:
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",""
...
Here is the the formula in excel:
=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)


Screenshot 2015-02-24 at 10.57.07 PM.png
29 KB   View   Download
------------------------------------