yogi_Compute The Nth Number Of Its Respective Occurrence of An Entity In An Array Of Entities

             Google Spreadsheet   Post  #1901

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

question by ksivanantha:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/APg4_Iyl7O4
How to find the count (nth Number) of a repeating word

Dear Forum Members,

Please help me to find the count (nth Number) of a repeating word based on the occurrence (First entry 1, 2nds similar word, 2,3,..)

Example : Supplier type is repeating below & first stationery supplier to get the rank 1 and second stationery supplier Q to get 2.

Name of Supplier    Type of Supplier   Supplier Rank

A                              Stationery                1

B                              Grocery                   1

C                              Grocery                    2

X                              House Hold               1

P                              Grocery                    3 

Q                              Stationery                 2

-------------------------------------------------------------------------------------------------------------------------

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

yogi_Consolidate Attributes of various Items In Rows To Unique Row Per Item And Attributes In Columns

             Google Spreadsheet   Post  #1899

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

question from comment by Michael Roberts in the following blog post
yogi_Consolidate Attributes of various Items In Rows To Unique Row Per Item And Attributes In Columns
http://yogi--anand-consulting.blogspot.com/2015/02/yogiconsolidate-attributes-of-various.html

yogi_Compute Row By Row Expense By Category For Week Numbers In Columns

             Google Spreadsheet   Post  #1898

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

post by  Marc Tyrrell:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/gAXRXs2y91M
Date range for expenses by type

Hi Folks,

I'm trying to set up a form which will allow for daily input of expenses and will produce a weekly summation of expenses by type.  The early version of the form is available athttps://docs.google.com/a/cavara.co/spreadsheets/d/1qj1aGuh9UzmrEd3A3jKSSNJ3Iu6yyEaYnVekiQG-_B4/edit#gid=0 .

What I want to do is have people enter their expenses using the form, which is recorded in the Form Response 1 tab, and use the Cumulative by week tab to create weekly summaries of the expense by type.

I am totally lost in trying to figure out how to do this!  If it was a database, I could, but not with sheets.  I would REALLY appreciate any suggestions anyone has!

Cheers,

Marc

---

Hi Chris,

Thanks - here you go.

https://docs.google.com/spreadsheets/d/1qj1aGuh9UzmrEd3A3jKSSNJ3Iu6yyEaYnVekiQG-_B4/edit?usp=sharing

Cheers,

Marc

----------------------------------------------------------------------------------------------------------------------------------------------

yogi_ Convert Row by Row Time Given In Column D In Text (Minutes and Seconds) Into Time In Numeric (hh colon mm colon ss)

             Google Spreadsheet   Post  #1897

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

post by  Ashlee Wall:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/u9yk-9krGoU
How to format a cell from 1 minute 30 seconds to 1:30
Please help!!! I am just starting to use Google sheets with a new job. They want me to transfer data from old sheet to new sheet. The old sheet is using 1 minute 30 seconds and they want new sheet to be formatted to say 1:30 instead. Thanks in advance for your help!!
----------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull From Data Brought In By Using ImportRange Those Rows That Have In Column F One Of The Entries In Cells A1 To B3

             Google Spreadsheet   Post  #1896

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

post by  Omar Kalaf:
ImportRange select everything that matches 6 of 12 possible entries in a spreadsheet

I have 1 spreadsheet that is my live data. In Col6 There are 12 possible options for cell content driven my data validation.

In a 2nd spreadsheet I am trying to pull some of that data in.

I am just wanting to pull data that matches 6 of those 12 possible cell contents AND ignore any empty cells

My 2nd sheet has the 6 criteria to match listed in cells A1 thru B3.

Trying this in cell A5 to pull the data but keep getting error.

=Query(ImportRange(ʺ1goG0TS1_2jwlGRetREYNRVk-Q6TEy3iWG_5VXFoZlusʺ,ʺMaster!A:Fʺ),ʺselect * where Col6 ='ʺ&A1&ʺ' or Col6='ʺ&A2&ʺ' or Col6='ʺ&A3&ʺ' or Col6='ʺ&B1&ʺ'or Col6='ʺ&B2&ʺ' or Col6='ʺ&B3&ʺ' ʺ,0)

It states unable to parse for Function Query parameter 2........

Any help would be most appreciated

--------------------------------------------------------------------------------------------------------------------------